Incorrect comparison of datetime and datetime2

Question

I know that it is not a good practice to have an implicit type convert. But this is really unexpected behavior when a lower value can suddenly become higher.

declare @LastSelectedDate DATETIME = '2021-11-09 13:52:29.187'
declare @LastSelectedDate_1 DATETIME2(7) = '2021-11-09 13:52:29.1866667'


SELECT IIF(@LastSelectedDate_1 > CAST(@LastSelectedDate AS DATETIME2), 1, 0)
SELECT IIF(@LastSelectedDate_1 > @LastSelectedDate, 1, 0)

Is this a bug or I am missing something? I am using sql server 2016.

Answer 1

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 |
+-----------------------------+-----------------------------+