SQL Server Performance

Date/Time conversion using CONVERT function

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by atulgoswami, Jan 24, 2011.

  1. atulgoswami New Member

    In one of the query, Date/Time is converted as CONVERT(VARCHAR(11), CreateDate,101) and then compared (greater than) as below
    CONVERT(VARCHAR(11), UpdateDate,101) > CONVERT(VARCHAR(11), AdmitDate,101)
    and surprisingly, it is not working as expected. By looking at the result, it looks that this conversion is not working properly for current month (i.e. any dates with January month is converted properly but failed in comparision).
    Now, why i am comparing after converions, is because need to fetch all the data updated before admit date but not on the same update date.

    I am not sure, why this strange behavior. But i changed it to as below and worked
    CAST(CONVERT(VARCHAR(11), UpdateDate,101) AS DateTime) > CAST(CONVERT(VARCHAR(11), AdmitDate,101) AS DateTime)
    Any idea/suggestion would be great help and if someone has also experienced then how it was handled.
    Thanks in advance
  2. Adriaan New Member

    To ignore the time part, use this syntax, rather than CONVERT:
    WHERE DATEADD(DAY, DATEDIFF(DAY, 0, UpdateDate), 0) > DATEADD(DAY, DATEDIFF(DAY, 0, AdmitDate), 0)
    I tried to reproduce the problem in a script, but it seems like the problem only occurs if UpdateDate and AdmitDate are varchar columns.
  3. Madhivanan Moderator

    where UpdateDate >DATEADD(DAY, DATEDIFF(DAY, 0, AdmitDate)+1, 0)

Share This Page