Comparing Dates in SQL

I found out the hard way (read: customer calling in to complain that the query I wrote him was omitting some events from the calendar query I wrote for him) to NEVER use basic math comparisons (=, <, >) for dates; at least, I have learned not to use them without DATEDIFF( ). It is more difficult sometimes, but it is going to render the correct result every time, with the other comparison operators will not.

The problem is that dates are not stored merely as dates but as datetime values. This means there is an hour, minute, second, and millisecond value associated with every date, even if that value is ignored by the user interface. In most cases this is not going to cause a mistake when comparing two different dates, but it does cause a problem when one is trying to find equivalence between two instances of the same date.

Here is the underlying "gotcha": When the time of day does not matter, most databases store dates as if they were at midnight of the day in question whereas the GETDATE( ) function returns the date and current time. Any calculations based on the GETDATE( ) value also have the current time. And because 1/1/2009 00:00:00 is technically less than 1/1/2009 15:39:15 (or any other time after midnight), those two values will not satisfy a comparison using "=" even though they signify the same calendar day.

Here is a simple CASE statement showing the use of DATEDIFF( ) in conjunction =, >, or <.

CASE
WHEN DATEDIFF(day, date1, date2) = 0 THEN "The dates are the same."
WHEN DATEDIFF(day, date1, date2) > 0 THEN "Date1 is earlier than Date2."
WHEN DATEDIFF(day, date1, date2) <>


Using an approach like this will always give you the results you want, without any problems from the time element of the datetime value.