Life, the Universe and the Internet

Just a quicky little Sql Server tip that I came across the other day.  If you are ever in a situation where you want to compare two dates in a where clause in a Transact SQL statement, but those dates actually have different times, for example:

Date1 =’01/09/2007 01:00:00′
Date2 = ‘01/09/2007 05:00:00′

You can’t just say “Where Date1 = Date2” because the times don’t match, but what you can do is in your where clause first reset both the times slots to midnight then do the where comparison, as per the example below:

SELECT SomeFields FROM SomeTable
WHERE CAST(FLOOR(CAST(Date1 AS FLOAT)) AS DATETIME) = CAST(FLOOR(CAST(Date2 AS FLOAT)) AS DATETIME)

Basically what this does is convert the date to a floating point, the floor command then basically rounds it down to the nearest integer, then it is converted back to a date time, the end result is that the dates now both look like this:

Date1 = “01/09/2007 00:00:00”
Date2 = “01/09/2007 00:00:00”

Hope this will be some use to someone.