Different time of day on date fields

Often when you insert a date in a datetime field, there is no associated time. (because you didn't provide one!) So, when you compare or join on a date field that did record a particular time of day, or you capture the current time of day "GETDATE()" you do not get a match.

You can set all time values associated with a date to the same value (00:00.0000) using the following code:

CAST(FLOOR(CAST(@yourdate AS FLOAT)) AS DATETIME)