Comparing SQL Server DateTime columns with SmallDateTime columns

Scenario

You have a table in SQL Server where you want to record (a) when the row was originally created and (b) when it was last modified.

For (a) you aren't interested in millisecond accuracy, so you may choose to use the SmallDateTime data type. You use the getDate() function as the default value for this column to automatically insert the current date and time (to the minute accuracy) when a row is inserted.

For (b) you are interested in millisecond accuracy (for replication purposes perhaps) but definitely you are interested in the seconds so that you could match it up with, say, a web server log file to find out who did it, and where they were (i.e. page URL etc). You use the getDate() function as the default value for this column to automatically insert the current date and time (to the millisecond accuracy) when a row is inserted, and create a trigger to also set it on updates.

Now let's say you want to identify all the rows that have never changed since they were created.

Obvious solution

Select * from table where a = b

Wrong. Because of the difference in data types, (a) will almost never equal (b) even if no row has ever been altered! This query returns zero rows, when we know for a fact there must be at least one row that has never been altered.

Less obvious solution

Select * from table where year(a) = year(b) and month(a) = month(b) and day(a) = day(b)

This query finds one row (out of 9000 in the table) in around 9 seconds. Not great.

Better solution

Select * from table where dateDiff(day, a, b) < 1

This query finds one row (out of 9000 in the table) in around 0.4 seconds. Now put that in your pipe and smoke it!

For the record, I was testing on MS SQL Server 2005 Express (64 bit) running on Windows 2008 R2 x64 as a virtual guest of Windows 2008 R2 Hyper-V x64 on a physical Dell server with two Intel Xeon quad core CPUs running at 2.6 GHz with 16 GB RAM. No other guest machines were running at the time.