Looking over the differences between DateTime and DateTime2, I noticed that the deprecated DateTime datatype has an odd time range of 00:00:00.000 through 23:59:59.997, whereas DateTime2 data type has a limit of 00:00:00.0000000 through 23:59:59.9999999.
I speculated that this limitation likely has something to do with earlier limitations, could someone please explain why this limitation is in place?
The weird limit and behavior (such as the last digit always being 0, 3 or 7) is because of how the time portion is internally stored in DATETIME:
The time portion is stored as the number of ticks since 00:00:00 (1 and 2). Each tick is exactly 1/300th of a second. Keeping this in mind, the DATETIME type is only capable of storing values such as:
00:00:00.000 -- 0 ticks
00:00:00.003 -- 1 tick, 1/300 = 0.003333 sec
00:00:00.007 -- 2 ticks, 2/300 = 0.006666 sec
00:00:00.010 -- 3 ticks, 3/300 = 0.01 sec
00:00:00.997 -- 299 ticks, 299/300 = 0.996666 sec
00:00:01.000 -- 300 ticks, 300/300 = 1 second
Since it is not possible to store half or quarter ticks, DATETIME cannot store or represent the values in between. For example, if you try to store a value such as CAST('2000-01-01 00:00:00.999' AS DATETIME) it will be rounded to 2000-01-01 00:00:01.000.
It has to do with the precision. You can make your DateTime2 definition have a similar precision to DateTime if you define it as DateTime2(3). A precision of 7 happens to be the default for DateTime2 which is what you are seeing in your question.
See also datetime2 (Transact-SQL) documentation
precision - 0 to 7 digits, with an accuracy of 100ns. The default precision is 7 digits.
See also datetime (Transact-SQL)
Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications
First thing first... DATETIME is not deprecated.
The reason for the discrepancy is that the original date time has a resolution of 3 milliseconds. The newer DATETIME2 has a resolution of 100 nanoseconds.
The "3 milliseconds" resolution of DATETIME is why you see 23:59:59.997 rather than 23:59:59.999 before rolling over to 00:00:00.000