Why is a datetime value returned that matches the predicate value when using greater than comparison

Question

I want to find any records greater than the time supplied in my predicate, which is coming from a C# web application in which there is only type DateTime. However, SQL Server is returning a row that is equal to the supplied predicate value. Why is it doing this? The ShipDateTimeUTC column is a datetime data type.

DECLARE @p__linq__1 datetime2(7) = '2024-08-07 00:14:11.3630000';
SELECT 
    [OrderHeaderId],
    Id,
    [ShipDateTimeUTC]
FROM

    [dbo].[ShippingContainerHeader]
WHERE 
    [ShipDateTimeUTC] > @p__linq__1;

Here are the results where you can see the ShipDateTimeUTC matches the predicate value. I don't want it to be included. Why is it being included in the results?

Answer 1

You will get this behaviour if ShipDateTimeUTC is a datetime datatype.

datetime2(7) has higher datatype precedence than datetime so you would expect the column to be implicitly cast to datetime2(7)

There is an annoying (†) "by design" behaviour that when casting to datetime2(7) values such as 2024-08-07 00:14:11.363 get converted to 2024-08-07 00:14:11.3633333 (and 2024-08-07 00:14:11.367 would be converted to 2024-08-07 00:14:11.3666667)

After this implicit conversion 2024-08-07 00:14:11.3633333 is larger than 2024-08-07 00:14:11.3630000.

You should change the datatype of @p__linq__1 to datetime to match the column as implicit conversions in the WHERE clause should be avoided anyway.

I initially thought that if for some reason you are stuck with datetime2(7) then you would need to change the value to use 2024-08-07 00:14:11.3633333 to take account of this. However the implicit comparison behaviour with mixed datatypes actually operates differently than with explicitly cast data types. Fiddle. This looks like a bug to me so reported here.

All of the below use an index seek despite the CASTs

DECLARE @T TABLE (DT DATETIME PRIMARY KEY)
INSERT @T values ('2024-08-07 00:14:11.363')
--Returns 0 rows as expected from above
SELECT *
FROM @T
WHERE DT = CAST('2024-08-07 00:14:11.3630000' AS datetime2(7))
--Returns 0 rows - unexpected
SELECT *
FROM @T
WHERE DT = CAST('2024-08-07 00:14:11.3633333' AS datetime2(7))
--Returns 1 rows so explicit cast to datetime2 helps
SELECT *
FROM @T
WHERE CAST(DT AS datetime2(7)) = CAST('2024-08-07 00:14:11.3633333' AS datetime2(7))
--Returns 1 rows - not sure why casting the column to the datatype it already has helps!
SELECT *
FROM @T
WHERE CAST(DT AS datetime) = CAST('2024-08-07 00:14:11.3633333' AS datetime2(7))

† The rationale for this behaviour (new under compat level 130) is

Improved precision when you convert to date/time types with higher precision. Be aware that datetime values are stored as ticks that represent 1/300th of a second. The newer time and datetime2 types store a discrete number of digits, where the number of digits matches the precision.

I have checked SQL Server 2000 Books online and that does say for datetime

Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds

So this behaviour is consistent with what was always documented even though it can cause potentially unexpected results.

Answer 2

Mixing datatypes is a bad idea. If the database type is datetime then the variable should be datetime.

The problem you have is the implicit cast of datetime to datetime2, for ShipDateTimeUTC, will produce 2024-08-07 00:14:11.3633333 which is greater than 2024-08-07 00:14:11.3630000.