Why CAST('20140904 23:59:59.999' AS datetime) equals '2014-09-05 00:00:00.000'? [duplicate]

Question

Try it for yourself, maybe it is somehow related to my environment:

The query:

SELECT CAST('20140904 23:59:59.999' AS datetime)

The result:

2014-09-05 00:00:00.000

Please explain this phenomenon.

UPDATE: Alex mentioned that datetime values are rounded to increments of .000, .003, or .007 seconds. The question is why?

Answer 1

datetime values are rounded to increments of .000, .003, or .007 seconds

I think you should use

SELECT CAST('20140904 23:59:59.997' AS datetime)

This returns:

 2014-09-04 23:59:59.997

You can find more information here: http://msdn.microsoft.com/en-us/library/ms187819.aspx

This is the accuracy of datetime function in SQL.

You could probably use datetime2 if you are using a newer version of MSSQL as the accuracy for that is 100 nanosecs.

Datetime2: http://msdn.microsoft.com/en-us/library/bb677335.aspx

Answer 2

The increments are part of the API spec: See this link.

datetime values are rounded to increments of .000, .003, or .007 seconds, as shown in the following table.

I found this link which has at the end a better explanation. Quote:

Actually, SQL Server does store there the clock-ticks since midnight. Each clock-tick is equivalent to 3.33 milliseconds. That’s also the reason why the DATETIME datatype has an accuracy of one three-hundredth of a second.

DATETIME is 2 x 4 bytes (date + time). The time part is stored as number of ticks since midnight. Each tick is 3.33 ms

If you need more precision, take a look at DATETIME2 type. DATETIME2 Accuracy is 100ns.