DATETIME data type precision for milliseconds is rounded to increments of .000, .003, or .007 which leads to some unexpected behavior from time to time. I.e. if we will try to extract at which day event logged with milliseconds occurred. The example below demonstrates it pretty: Event occurring at 20101205 23:59:999 will be assigned to day 6 instead of 5. Even if it looks like to be a minor issue I saw few serious issues it has caused in an online system for financial transactions processing.
SELECT '20101205 23:59:000', DAY(CAST('20101205 23:59:59:000' AS DATETIME))
UNION ALL
SELECT '20101205 23:59:997', DAY(CAST('20101205 23:59:59:997' AS DATETIME))
UNION ALL
SELECT '20101205 23:59:998', DAY(CAST('20101205 23:59:59:998' AS DATETIME))
UNION ALL
SELECT '20101205 23:59:999', DAY(CAST('20101205 23:59:59:999' AS DATETIME))
UNION ALL
SELECT '20101206 00:00:000', DAY(CAST('20101206 00:00:00:000' AS DATETIME))
Using the modern DATETIME2 data type introduced in SQL Server 2008 no such issue exists:
SELECT '20101205 23:59:000', DAY(CAST('20101205 23:59:59:000' AS DATETIME2)) UNION ALL
SELECT '20101205 23:59:997', DAY(CAST('20101205 23:59:59:997' AS DATETIME2)) UNION ALL
SELECT '20101205 23:59:998', DAY(CAST('20101205 23:59:59:998' AS DATETIME2)) UNION ALL
SELECT '20101205 23:59:999', DAY(CAST('20101205 23:59:59:999' AS DATETIME2)) UNION ALL
SELECT '20101206 00:00:000', DAY(CAST('20101206 00:00:00:000' AS DATETIME2))
