SQL Server 2008 and milliseconds

Question

In SQL Server 2008, why do the following queries return the same value?

-- These all return 2011-01-01 23:59:59.997
SELECT CAST('2011-01-01 23:59:59.997' as datetime)
SELECT CAST('2011-01-01 23:59:59.998' as datetime)

And why does the following query round to the next day?

-- Returns 2011-01-02 00:00:00.000
SELECT CAST('2011-01-01 23:59:59.999' as datetime)

Answer 1

The MSDN docs for datetime at http://msdn.microsoft.com/en-us/library/ms187819.aspx say

Time range == 00:00:00 through 23:59:59.997 Accuracy == Rounded to increments of .000, .003, or .007 seconds

In the linked document there is also a section "Rounding of datetime Fractional Second Precision".

datetime2 gives you more accuracy.