Hi, In one of the query, Date/Time is converted as CONVERT(VARCHAR(11), CreateDate,101) and then compared (greater than) as below WHERE 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 -
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.