Post reply

Text Date Comparison

  • March 8, 2007 at 4:38 am

    #172630

    I had to fix a sp that was returning incorrect results. The section that was causing the problem was:

    SELECT.....
    FROM ....
    WHERE CONVERT(VARCHAR(10),(CONVERT(DATETIME, record_date,103)),103) >= '01/09/2006'

    This code returned all rows but none for any with a record_date of 01/02/2007. To resolve this problem I re-wrote the script as follows:

    SELECT.....
    FROM ....
    WHERE CONVERT(DATETIME, record_date,103)),103) >= '01/09/2006'

    My assumption therefore was that the problem was being caused by the text comparison i.e. '01/02/2007' >= '01/09/2006'. What I cannot explain is why. Can anyone shed some light on this please?

    Thanks

  • March 8, 2007 at 4:40 am

    #693761

    Sorry, the amended script was:

    SELECT.....
    FROM ....
    WHERE CONVERT(DATETIME, record_date,103) >= '01/09/2006'

  • March 8, 2007 at 4:49 am

    #693764

    Hi ,

    David first you have to see the format of record_date for e.g.

    MY Record_Dtae field which contain a date value like "20070308' so iwill use the query like :

    select convert(varchar,convert(datetime, '20070308',112),103)

    Regards ,

    Amit Gupta..

     

  • March 8, 2007 at 12:58 pm

    #693910

    You were comparing 2 varchar values instead of datetime values.

    In this case the varchar value 01/02/2007 is less than the varchar value 01/09/2006 because it is looking character by character and 2 is less than 9.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply