SQL Server behaviour change while using datetimeoffset

user_6281255 0
Viewed 157

I have been using SQL Server for quite sometime and noticed a change in behavior while using datetimeoffset.

Lets take an example of two queries, one is using normal string comparison in datetime values and one which is using datetimeoffset type viz

select * from (
    select cast('2019-09-06 11:28:05.757' as datetime) as test_date
) data where test_date < '2019-09-06 11:28:05.757'



DECLARE @P1 datetimeoffset;
set @P1 = '2019-09-06 11:28:05.757';

select * from (
    select cast('2019-09-06 11:28:05.757' as datetime) as test_date
) data where test_date < @P1

I executed the above queries in SQL Server 2014 and SQL Server 2019 and the difference comes in the execution of second query, where for SQL Server 2019 it gives the results as the corresponding date

enter image description here

I wanted to check if there is some change in behavior with respect to the datetimeoffset parameter type in the latest versions of SQL Server.

sql-server-2019 sql-server sql
2 Answers

The behaviour you have both expected and also documented in SQL Server 2016's breaking changes.

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. For more information, see this Microsoft Support Article.

With 2016 Microsoft updated the behaviour for converting from the old datetime data type to the newer ones to be more accurate. Previously a datetime value like 2019-09-06 11:28:05.757 converted to the datetime2(7) value 2019-09-06 11:28:05.7570000, however, in 2016+ it is now accurately converted to 2019-09-06 11:28:05.7566667. This is because a datetime is accurate to 1/300 of a second, not 1/1000, so the conversion before was actually adding time to the value.

For your query, you firstly have the datetimeoffset(7) value 2019-09-06 11:28:05.7570000. You then compare that value to the datetime value 2019-09-06 11:28:05.757. As the 2 data types are different one is implicitly converted to the other. datetimeoffset has a higher precendence, so the datetime value (2019-09-06 11:28:05.757) is converted to a datetimeoffset the value 2019-09-06 11:28:05.7566667, which is less that your other datetimeoffset value 2019-09-06 11:28:05.7570000.

As mentioned, in 2014 (and prior) SQL Server inaccurately converted the datetime value to 2019-09-06 11:28:05.7570000, which isn't less than itself. This is why the behaviour differs.

If you want the same behaviour, I would suggest (as the breaking change does), using an explicit conversion, and convert to a datetimeoffset(3). For example, the below does not return a result set in either instance:

DECLARE @P1 datetimeoffset;
SET @P1 = '2019-09-06 11:28:05.757';

SELECT *
FROM (SELECT cast('2019-09-06 11:28:05.757' as datetime) as test_date) data
WHERE CONVERT(datetimeoffset(3),test_date) < @P1;

2014, 2019 (Linux)

user_20299830

Test case

select cast(test_date as datetimeoffset) d2ofs, @@version
from (
    select cast('2019-09-06 11:28:05.757' as datetime) as test_date
) data

Results:

d2ofs   (No column name)
2019-09-06 11:28:05.7566667 +00:00  Microsoft SQL Server 2016 (SP2-CU12) (KB4536648) - 13.0.5698.0 (X64) 
    Feb 15 2020 01:47:30 
    Copyright (c) Microsoft Corporation
    Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

vs

d2ofs   (No column name)
2019-09-06 11:28:05.7570000 +00:00  Microsoft SQL Server 2014 (SP3-CU-GDR) (KB4535288) - 12.0.6372.1 (X64) 
    Dec 12 2019 15:14:11 
    Copyright (c) Microsoft Corporation
    Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 17763: ) (Hypervisor)

db<>fiddle

user_62199790
Related