yeah, I have no idea why they thought this behaviour would be a good idea.
It is "by design" rather than a bug though
An example of a breaking change protected by compatibility level is an implicit conversion from datetime to datetime2 data types. Under Database Compatibility Level 130, these show improved accuracy by accounting for the fractional milliseconds, resulting in different converted values. To restore previous conversion behavior, set the Database Compatibility Level to 120 or lower.
Specifically datetime
that end 3
are treated as though it was 3
recurring and datetime
ending 7
are treated as though it was 6
recurring. datetime
that end 0
are unaffected. (these were the only possibilities for that datatype as it has 300 "ticks" per second)
Restricting compatibility level to solve this single issue seems a sledge hammer solution. You can explicitly cast it to datetime2(3)
to avoid this
DECLARE @LastSelectedDate DATETIME = '2021-11-09 13:52:29.187'
SELECT CAST(@LastSelectedDate AS DATETIME2(7)),
CAST(CAST(@LastSelectedDate AS DATETIME2(3)) AS DATETIME2(7))
Returns
+-----------------------------+-----------------------------+
| 2021-11-09 13:52:29.1866667 | 2021-11-09 13:52:29.1870000 |
+-----------------------------+-----------------------------+