Skip to main content
1 of 2
Martin Smith
  • 86.5k
  • 15
  • 252
  • 342

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.

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 |
+-----------------------------+-----------------------------+
Martin Smith
  • 86.5k
  • 15
  • 252
  • 342