TSQL: Accuracy of DATETIME

Here’s something I didn’t know about the DATETIME data type in SQL Server….

SELECT CAST('2014-04-10 00:00:00.000' AS DATETIME)
UNION ALL
SELECT CAST('2014-04-10 00:00:00.001' AS DATETIME)
UNION ALL
SELECT CAST('2014-04-10 00:00:00.002' AS DATETIME)
UNION ALL
SELECT CAST('2014-04-10 00:00:00.003' AS DATETIME)
UNION ALL
SELECT CAST('2014-04-10 00:00:00.004' AS DATETIME)
UNION ALL
SELECT CAST('2014-04-10 00:00:00.005' AS DATETIME)
UNION ALL
SELECT CAST('2014-04-10 00:00:00.006' AS DATETIME)
UNION ALL
SELECT CAST('2014-04-10 00:00:00.007' AS DATETIME)
UNION ALL
SELECT CAST('2014-04-10 00:00:00.008' AS DATETIME)
UNION ALL
SELECT CAST('2014-04-10 00:00:00.009' AS DATETIME)

The resultset will be as follows…

2014-04-14 00:00:00.000
2014-04-14 00:00:00.000
2014-04-14 00:00:00.003
2014-04-14 00:00:00.003
2014-04-14 00:00:00.003
2014-04-14 00:00:00.007
2014-04-14 00:00:00.007
2014-04-14 00:00:00.007
2014-04-14 00:00:00.007
2014-04-14 00:00:00.010

The sharp eyed amongst you would have noticed some values milliseconds have been rounded. This is explained the the “accuracy section of the documentation for DATETIME

Rounded to increments of .000, .003, or .007 seconds

If this is an issue for your application then you should consider using DATETIME2.


Leave a Reply

Current ye@r *