SQL Date Comparison

When comparing the datetime datatype in SQL Server, it is important to maintain consistency in order to gaurd against SQL interpreting a date differently than you intend. In at least one occasion I have seen someone specify a short format for a date, like (1/4/08) only to find that SQL interpreted the month as the year. A couple incidences similar to this has scared me enough to be very careful in my SQL Date comparisons.

Most of the time when hard coding a date I define it out the way SQL Server defines it (at least according to SQL Profiler). Like this:
[cc lang=”sql”]
DECLARE @MyDate datetime

SET @MyDate = ‘2000-01-04T00:00:00.000’

SELECT *
FROM Customers
WHERE ModifiedDate = @MyDate
[/cc]
When defined this way, SQL will have no option to interpret in any other way that what is presented. The downside is, yes, it’s long. However when hard coding dates, I’d personally rather be safe that sorry. I haven’t run into it, nor have I tested it, but I’d hate to have all my dates coded in a system changed just because the next version of SQL or any other system decided to interpret things differently.

The other safe way to compare dates is to use SQL Server’s datepart function. Utilizing this method, you can never go wrong either.

[cc lang=”sql”]
SELECT *
FROM Customers
WHERE
— Compare year
DATEPART(yyyy, ModifiedDate) = 2000
— compare month
AND DATEPART(mm, ModifiedDate) = 08
— compare day
AND DATEPART(dd, ModifiedDate) = 01
[/cc]

Though this is a little more of a pain, and there may be a fraction of a fraction time difference for the conversion, it’s worth the peace of mind.