Skip to main content
added 273 characters in body
Source Link
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.

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

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

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