How does Microsoft SQL Server compare datetime to datetimeoffset values in a query statement?

Question

I'm working with a legacy database (running on SQL Server 2019) which uses datetime for columns storing timestamp information and I'm trying to get a better handle on what SQL Server is doing under the hood when comparing a datetime value to a datetimeoffset value.

We're attempting to migrate some code to a new platform which will use the Microsoft SQL Server JDBC drivers. In order to run the database in Compatibility Level 150 (for SQL Server 2019), the Microsoft JDBC driver will translate all timestamps into datetimeoffset parameters. This is causing precision issues when the datetime value ends in either 3 or 7.

I've been reading through other Stack Overflow response (such as SQL Server behaviour change while using datetimeoffset & [x][2]) and understand how SQL Server coerces a datetime value into datetimeoffset, but that coercion does not appear to be exactly what is happening in a WHERE clause on a SQL Statement.

For example, take the following SQL:

declare @tmp table (id int identity primary key, createDate datetime not null)
insert into
    @tmp
        (createDate)
    values
          ('2021-09-27 18:36:01.930')
        , ('2021-09-27 18:36:01.933')
        , ('2021-09-27 18:36:01.937')

declare @input datetimeoffset = '2021-09-27 18:36:01.937'

select
    *
from
    @tmp
where
    createDate = @input

When running this query, you get no matches.

I know that if you run select cast('2021-09-27 18:36:01.937' as datetimeoffset) you get 2021-09-27 18:36:01.9370000 +00:00 and if you run select cast(cast('2021-09-27 18:36:01.937' as datetime) as datetimeoffset) you get 2021-09-27 18:36:01.9366667 +00:00, so that potentially make sense on why the values might not match if the createDate column is being implicitly converted to a datetimeoffset. However, when I look at the execution plan this does not appear to be the case and if I change the code to:

declare @tmp table (id int identity primary key, createDate datetime not null)
insert into
    @tmp
        (createDate)
    values
          ('2021-09-27 18:36:01.930')
        , ('2021-09-27 18:36:01.933')
        , ('2021-09-27 18:36:01.937')

declare @input datetimeoffset = '2021-09-27 18:36:01.9366667 +00:00'

select
    *
from
    @tmp
where
    createDate = @input

I still do not get a match, which would appear to mean that createDate is not being coerced into a datetimeoffset datatype for comparison. Now if I explicitly cast the datetimeoffset to datetime, I do get a match:

declare @tmp table (id int identity primary key, createDate datetime not null)
insert into
    @tmp
        (createDate)
    values
          ('2021-09-27 18:36:01.930')
        , ('2021-09-27 18:36:01.933')
        , ('2021-09-27 18:36:01.937')

declare @input datetimeoffset = '2021-09-27 18:36:01.937'

select
    *
from
    @tmp
where
    createDate = cast(@input as datetime)

Or if I try to find match the 2021-09-27 18:36:01.930 value, I do not need to explicitly cast the value:

declare @tmp table (id int identity primary key, createDate datetime not null)
insert into
    @tmp
        (createDate)
    values
          ('2021-09-27 18:36:01.930')
        , ('2021-09-27 18:36:01.933')
        , ('2021-09-27 18:36:01.937')

declare @input datetimeoffset = '2021-09-27 18:36:01.930'

select
    *
from
    @tmp
where
    createDate = @input

This tells me there is some kind of conversion going on, but I cannot figure out what's happening under the hood.

Can anyone help me understand what SQL Server is doing under the hood when it runs the following?

declare @tmp table (id int identity primary key, createDate datetime not null)
insert into
    @tmp
        (createDate)
    values
          ('2021-09-27 18:36:01.930')
        , ('2021-09-27 18:36:01.933')
        , ('2021-09-27 18:36:01.937')

declare @input datetimeoffset = '2021-09-27 18:36:01.937'

select
    *
from
    @tmp
where
    createDate = @input

I am trying to see if there is some way to format timestamp data so that all the existing code will not break. I could drop just drop the millisecond precision to the 1/100 (instead of 3.33ms precision), but I would like to keep as much precision as possible. I originally started down the path of converting the datetime values to datetime2, but due to the volume of data, indices, stats, etc. around the data converting everything would require a fair amount of downtime. Not to mention the code uses various datetime math tricks that would need to be refactored.

I know I could go through all the code and also add explicit conversions of the datetimeoffset to datetime as well, but some of the code is being generated by fluent SQL builders so doing that is not exactly straightforward either.

Lastly, I know I could convert the values to a varchar(23) field and allow SQL Server to do the implicit conversion that way, but I'd like to avoid that if possible.

So, if anyone can shed light on what SQL Server is doing internally to compare the datetime to datetimeoffset values, I would greatly appreciate it.

RANT — I really don't know why Microsoft changed SQL Server 2016 to convert values like 2021-09-27 18:36:01.937 to 2021-09-27 18:36:01.9366667 when casting to datetimeoffset and datetime2. Especially since casting a datetimeoffset(3) or datetime2(3) to a datetimeoffset would result in 2021-09-27 18:36:01.9370000 +00:00. For example:

select 
    cast(cast('2021-09-27 18:36:01.937' as datetime) as datetimeoffset) as [datetime]
  , cast(cast('2021-09-27 18:36:01.937' as datetimeoffset(3)) as datetimeoffset) as [datetimeoffset(3)]
  ,   cast(cast('2021-09-27 18:36:01.937' as datetime2(3)) as datetimeoffset) as [datetime2(3)]

Results in: | datetime | datetimeoffset(3) | datetime2(3) | |--|--|--| | 2021-09-27 18:36:01.9366667 +00:00 | 2021-09-27 18:36:01.9370000 +00:00 | 2021-09-27 18:36:01.9370000 +00:00 |

It would seem to be more consistent if they were all cast the same way. I know the change was supposedly made to improve precision accuracy, but my guess is it causes way more problems than it solves.

Answer 1

OK, the problem here is the implicit conversion. As you have noted, when you convert a datetime to a datetimeoffset on recent versions of SQL Server (SQL Server 2016+), the value is correctly converted to be accurate to 1/300th of a second. For example the time 18:36:01.937 when converted to a datetimeoffset(7) (or even a datetime2(7)) would be 18:36:01.9366667. (This is not the case on older versions of SQL Server, that would incorrectly convert the value to 18:36:01.9370000.)

You define your parameter as a datetimeoffset, no precision (bad idea), so this defaults to a datetimeoffset(7). As such the value 2021-09-27T18:36:01.937 becomes 2021-09-27T18:36:01.9370000+00:00.

The datetime value here is implicitly converted to a datetimeoffset as the latter has a higher Data Type Precedence. So your 3 values become:

2021-09-27T18:36:01.9300000+00:00
2021-09-27T18:36:01.9333333+00:00
2021-09-27T18:36:01.9366667+00:00

As you can see, none of these are the same value as your variable @input, which as the value 2021-09-27T18:36:01.970000+00:00.

One would think therefore that defining the precision of @input to be 3 would fix this, alas it does not. It seems that behind the scenes SQL Server still implicitly converts the datetime to a datetimeoffset(7) when it does it's comparison.

So the solution is the therefore use explicit casting, which is also stated in the documentation:

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, as seen in the example above. Use explicit casting to datetime2 datatype whenever a mixed comparison scenario between datetime and datetime2 datatypes exists. For more information, refer to this Microsoft Support Article.

As the table you are querying has a datetime you want to explicitly convert your parameter to a datetime, not convert the column, to maintain SARGability: So the correct query you want is:

DECLARE @tmp table (id int IDENTITY PRIMARY KEY,
                    createDate datetime NOT NULL);
INSERT INTO @tmp (createDate)
VALUES ('2021-09-27T18:36:01.930'),
       ('2021-09-27T18:36:01.933'),
       ('2021-09-27T18:36:01.937');

DECLARE @input datetimeoffset(3) = '2021-09-27T18:36:01.937';

SELECT createDate
FROM @tmp
WHERE createDate = CONVERT(datetime, @input);

If it were the other way round, and your column was a datetimeoffset and the parameter a datetime then you would convert said parameter to a datetimeoffset(3):

DECLARE @tmp table (id int IDENTITY PRIMARY KEY,
                    createDate datetimeoffset NOT NULL);
INSERT INTO @tmp (createDate)
VALUES ('2021-09-27T18:36:01.930'),
       ('2021-09-27T18:36:01.933'),
       ('2021-09-27T18:36:01.937');

DECLARE @input datetime = '2021-09-27T18:36:01.937';

SELECT createDate
FROM @tmp
WHERE createDate = CONVERT(datetimeoffset(3), @input);

---

To address your "rant":
Because the "new" method is more accurate (as I state earlier). The datetime value 2021-09-27T18:36:01.937 is not the value 2021-09-27T18:36:01.9370000~. A datetime is accurate to 1/300 of a second. This is why datetime values all end in 0, 3, or 7; the latter 2 are 0.0033333~ and 0.0066666~ seconds rounded to 3 decimal places. As a result when you convert a datetime value to a datetime2(7), for example, it makes perfect sense that the converted value respects the prior accuracy, and so the datetime value 2021-09-27T18:36:01.937 is converted to the datetime2(7) value 2021-09-27T18:36:01.9366667 (which is 2021-09-27T18:36:01.9366666666~ rounded to precision of 7).