Results 1 to 7 of 7

Thread: date Convertion

  1. #1
    Join Date
    Mar 2004
    Posts
    202

    Unanswered: date Convertion

    Is this kind of coding correct?

    SELECT *
    FROM mainAim
    WHERE CONVERT(varchar,Create_time, 120) = '2004-004-23 07:00:05'

    I try to bring out the data related to the where clause but nothing showed up.
    Is my where clause there got problem?
    the data type for the field Create_time is datetime.
    Please help.
    Thanx

  2. #2
    Join Date
    Mar 2003
    Location
    Atlanta, GA
    Posts
    191
    Not too sure if this is your only issue, but I think CONVERT returns date data under the "120" style spec as yyyy-mm-dd hh:mi:ss

    Your hardcoded date is in the form yyyy-mmm-dd hh:mi:ss (note that you have 3 digits for the month.

    Hope this helps.

    Tim
    Tim

  3. #3
    Join Date
    Mar 2004
    Posts
    202
    Hehe.................
    thanx..........................i'll go try and see.
    Thanx again

  4. #4
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    datetime datatype converts implicitly to nvarchar. You shouldn't need to CAST or CONVERT to compare a datetime field, and is probably going to cause more problems then it's worth. By comparing a string containing a date/time to a datetime field, the string is converted implicitly to datetime and compared (this allows you to do greater-than, greater-than-or-equal-to, less-than, etc comparisons). But if you convert the datetime field to a string, then your "=" is doing a string level comparison, not a datetime level comparison. So a string level comparison of "2004-03-05" is different than "3/5/2004". But a datetime level comparison "2004-03-05" is equivilant to "3/5/2004".
    That which does not kill me postpones the inevitable.

  5. #5
    Join Date
    Mar 2004
    Posts
    202
    Thanx for your advice.
    I really appreciate it.
    Thanx again

  6. #6
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    As a side note, the formats for CONVERT and CAST (which are functionally similar) are:

    CAST ( expression AS data_type )
    CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

    So converting a datetime to an nvarchar might look like:
    CAST( myDate AS nvarchar(30) )
    or
    CONVERT( nvarchar(30), myDate )

    Or as MrWizard mentioned, you can add a style to CONVERT for datetime conversions:

    CONVERT( nvarchar(30), myDate, 120 )
    Style 120 = ODBC canonical: yyyy-mm-dd hh:mi:ss(24h)
    That which does not kill me postpones the inevitable.

  7. #7
    Join Date
    Mar 2004
    Posts
    202
    Thanx Seppuku.
    I appreciate it

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •