Comparing Dates

Prior to SQL Server 2008, there’s no such thing as a date. There is only datetime. Because of the time, you can’t simply use a WHERE clause like this:

WHERE (@MyFirstDate = getdate())

They’ll never match! Unless it’s actually exactly midnight.

It gets even more unpredictable if you’re making relative comparisions:

WHERE (@MyFirstDate > '3/2/2015')

If @MyFirstDate is ‘3/2/2015 3:27 PM’, that WHERE clause will actually return True. If the date parts are the same, you’re actually asking if 3:27 PM > 12:00 AM.

To reduce the uncertainty, you should always use DATEDIFF. If you’re comparing just dates, you can specify to compare based on days where the difference in days is zero.

WHERE (DATEDIFF(d, @MyFirstDate, getdate()) = 0)

Luckily, in 2008 they introduced the date and time data types. Now you can store just the part you need and avoid all this silliness.

Here’s some light reading for other date and time functions: Date and Time Data Types and Functions

Advertisements