15

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.

Improve this question

2 Answers 2

Reset to default
19

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 |
+-----------------------------+-----------------------------+
Improve this answer
0
3

Background

This is a question of interpretation. What does your datetime value represent?

'2021-11-09 13:52:29.187'

This is one of the possible string representations of a particular datetime value.

The real value is 44,507 days and 14,984,756 ticks after 1 January 1900, where a tick is 1/300th of a second:

DECLARE @LastSelectedDate datetime = {TS '2021-11-09 13:52:29.187'};
DECLARE @AsBinary binary(8) = CONVERT(binary(8), @LastSelectedDate);

SELECT
    [Days] = CONVERT(integer, SUBSTRING(@AsBinary, 1, 4)),
    [Ticks] = CONVERT(integer, SUBSTRING(@AsBinary, 5, 4));
Days Ticks
44,507 14,984,756

Note that 1/300s = 0.333... where the digit 3 is infinitely recurring.

Stated most accurately, the datetime value is 2021-11-09 13:52:29.18666... where the 6 repeats forever.

Before compatibility level 130

SQL Server rounds datetime values with a recurring 3 or 6 in the third decimal place to 3 or 7, respectively. It performs this rounding both for display and for internal calculations, including conversion to another data type.

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 120;
GO
DECLARE @LastSelectedDate datetime = {TS '2021-11-09 13:52:29.187'};

-- Retuns 187
SELECT DATEPART(MILLISECOND, @LastSelectedDate);
-- Returns 187,000
SELECT DATEPART(MICROSECOND, @LastSelectedDate);
-- Returns 187,000,000
SELECT DATEPART(NANOSECOND, @LastSelectedDate);

Compatibility level 130 and later

SQL Server still rounds for display, but it uses the more accurate value for internal calculations and conversions.

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 130;
GO
DECLARE @LastSelectedDate datetime = {TS '2021-11-09 13:52:29.187'};

-- Retuns 186
SELECT DATEPART(MILLISECOND, @LastSelectedDate);
-- Returns 186,666
SELECT DATEPART(MICROSECOND, @LastSelectedDate);
-- Returns 186,666,666
SELECT DATEPART(NANOSECOND, @LastSelectedDate);

Conversions and comparisons

For conversions, the most accurate datetime value is converted to its closest representation in the target data type. For example, converting to datetime2(7) results in rounding in the seventh decimal place because the recurring 6 cannot be stored exactly:

DECLARE @Original datetime = {TS '2021-11-09 13:52:29.187'};
DECLARE @Converted datetime2(7) = CONVERT(datetime2(7), @Original);

SELECT 
    Original = @Original, 
    Converted = @Converted;
Original Converted
2021-11-09 13:52:29.187 2021-11-09 13:52:29.1866667

Note that the converted value is not equal to either the displayed datetime value or the accurate value (which ends in infinite sixes). In fact, the original value compares as less than the converted value, despite what the displayed values would lead you to believe:

DECLARE @Original datetime = {TS '2021-11-09 13:52:29.187'};
DECLARE @Converted datetime2(7) = CONVERT(datetime2(7), @Original);

SELECT 
    Original = @Original, 
    Converted = @Converted
WHERE
    @Original < @Converted;
Original Converted
2021-11-09 13:52:29.187 2021-11-09 13:52:29.1866667

The displayed datetime value 2021-11-09 13:52:29.187 is considered less than the datetime2(7) value 2021-11-09 13:52:29.1866667. This may be counterintuitive but remember the accurate datetime value has a never-ending stream of sixes at the end, while the converted value has been rounded up to seven in the seventh decimal place. It is technically correct.

Work around

The Breaking changes in SQL Server 2016 document says (emphasis added):

Under database compatibility level 130, implicit conversions from datetime to datetime2 data types show improved accuracy by accounting for the fractional milliseconds, resulting in different converted values. Use explicit casting to datetime2 datatype whenever a mixed comparison scenario between datetime and datetime2 datatypes exists.

The issue above is in the predicate @Original < @Converted because @Original is datetime and @Converted is datetime2(7). We can get the expected results by explicitly casting the datetime variable to datetime2:

DECLARE @Original datetime = {TS '2021-11-09 13:52:29.187'};
DECLARE @Converted datetime2(7) = CONVERT(datetime2(7), @Original);

-- No longer less than
SELECT 
    Original = @Original, 
    Converted = @Converted
WHERE
    CONVERT(datetime2(7), @Original) < @Converted;

-- Compares equal
SELECT 
    Original = @Original, 
    Converted = @Converted
WHERE
    CONVERT(datetime2(7), @Original) = @Converted;

Results:

Original Converted

(no rows)

Original Converted
2021-11-09 13:52:29.187 2021-11-09 13:52:29.1866667

For more details and examples, see my article Don't Mix with Datetime.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.