DateTime comparisons in SQL Server 2008 and 2012

The issue is that SQL Server’s DateTime data type appears to be able to resolve milliseconds – but it really does not. It rounds to the nearest 0, 3, or 7.
Consider the following:

select
	case
		when convert(datetime, '9/1/08 00:00:00.005')
			 between
			convert(datetime, '9/1/08 00:00:00.006')
			 and
			convert(datetime, '9/1/08 00:00:00.007')
		then 'true'
		else 'false'
	end

----
true

(1 row(s) affected)

This query returns ‘true’ because all three datetime values are rounded to 007 milliseconds when converted:

select convert(varchar(32), convert(datetime,
                                '9/1/08 00:00:00.005'), 121);

---
2008-09-01 00:00:00.007
(1 row(s) affected)

In C# and .Net on the other hand, the DateTime type resolves milliseconds correctly:

MessageBox.Show(this, DateTime.Parse("
             9/1/08 00:00:00.001").ToString("HH:mm:ss.ffff"));

Output: 00:00:00.0010

This can have very undesirable effects when you pass parameters from .Net into a SQL query. Your DateTime value gets rounded in the database. Range comparisons can become unpredictable. If your query compares the passed-in date parameter to other dates in the database, if may fall outside a range, even though in reality it is in the range.

Similarily, a set of different DateTime values in .Net could become the same values in SQL, creating duplicates where you don’t expect them.

The reason for this is that SQL Server’s internal resolution for DateTime is 3.33 ms (as explained by Steve Kozak in SQL Server Professional) – which is larger than the least significant digit.