Just wanted to see if anyone has ever experienced any problems, using comparison operators with DATETIME variables in SQL2K SP4?It looks like comparison is not being evaluated correctly. For example, check out the WHERE clause of the following query. When I run it the way it is -- it returns some records outside specified range. If I take out the variables, and use the exact same hard-coded dates -- it works as expected. Re-writing WHERE clause, using BETWEEN instead of comparison operators also works fine. declare @from_dt datetimedeclare @to_dt datetime-- Set the @from_dt and @to_dtselect @from_Dt = CONVERT(DATETIME,'Apr 1 2007 12:00:01:000AM'), @to_dt = CONVERT(DATETIME,'Apr 30 2007 11:59:59:000PM') -- verify that vars match the hard-coded valuesselect @from_Dt as var_from_dt, CONVERT(DATETIME,'Apr 1 2007 12:00:01:000AM') as hard_from_dt, @to_dt as var_to_dt,CONVERT(DATETIME,'Apr 30 2007 11:59:59:000PM') as hard_to_dtSELECT a.MACHINE_EVENT_ID id ,EVENT_BEGIN_LOCAL_DTTMFROM VW_FCT_MACHINE_EVENT AWHERE A.APPLICATION_USER_ID = '32D6E3EC-B1E9-4D28-9AD2-FE044F1A2BB3' AND A.EVENT_BEGIN_LOCAL_DTTM >= @from_dt ---CONVERT(DATETIME,'Apr 1 2007 12:00:01:000AM') AND A.EVENT_BEGIN_LOCAL_DTTM < @to_dt ---CONVERT(DATETIME,'Apr 30 2007 11:59:59:000PM')select @from_Dt as var_from_dt, CONVERT(DATETIME,'Apr 1 2007 12:00:01:000AM') as hard_from_dt, @to_dt as var_to_dt, CONVERT(DATETIME,'Apr 30 2007 11:59:59:000PM') as hard_to_dt
Datetime variables and comparison operators
Posted - 2007-06-22 : 14:16:30
