Odd (and inconsistent) behaviour with datetime/datetime2 comparisons

Test results for SQL Server feedback submission:

https://feedback.azure.com/d365community/idea/2b294fcf-5f55-ef11-b4ad-000d3add4ccc

---

Tested against all available compat levels for docker tags:

• 2022-latest
• 2019-latest
• 2017-latest

Test script

-- ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 160 -- 150, 140, 130, 120, 110, 100
-----------------------------------------------

-----------------------------------------------
-- Shared
DECLARE @DT datetime = '2024-08-07 00:14:11.363';
DECLARE @DT2 datetime2 = CONVERT(datetime2, @DT);
-----------------------------------------------

-----------------------------------------------
-- with primary key
DECLARE @T table (DT datetime PRIMARY KEY);
INSERT @T VALUES (@DT);

SELECT * FROM @T WHERE DT = @DT2;
SELECT * FROM @T WHERE CONVERT(datetime2, DT) = @DT2;
SELECT * FROM @T WHERE DT = CONVERT(datetime, @DT2);
-----------------------------------------------

-----------------------------------------------
-- without primary key
DECLARE @T2 table (DT datetime);
INSERT @T2 VALUES (@DT);

SELECT * FROM @T2 WHERE DT = @DT2;
SELECT * FROM @T2 WHERE CONVERT(datetime2, DT) = @DT2;
SELECT * FROM @T2 WHERE DT = CONVERT(datetime, @DT2);

Test results

Y = Yes, a row was returned
N = No, no rows returned

| Compat level | DT = @DT2 | CONVERT(datetime2, DT) = @DT2 | DT = CONVERT(datetime, @DT2) | 
|--------------|-----------|-------------------------------|------------------------------| 
| 160          | N         | Y                             | Y                            | 
| 150          | N         | Y                             | Y                            | 
| 140          | N         | Y (N for 2017-PK)             | Y                            | 
| 130          | N         | Y (N for 2017-PK)             | Y                            | 
| 120          | Y         | Y                             | Y                            | 
| 110          | Y         | Y                             | Y                            | 
| 100          | Y         | Y                             | Y                            | 

Column descriptions

• dt = @dt2
  • Tested comparing column of type datetime directly compared to datetime2 variable
• CAST(dt AS dt2) = @dt2
  • Tested comparing column of type datetime cast to datetime2 compared to datetime2 variable
• dt = CAST(@dt2 AS dt)
  • Tested comparing column of type datetime to datetime2 variable cast to datetime

Result findings

Looks like in nearly all cases the behavior was consistent across SQL Server versions...except for compat levels 130 and 140 for SQL Server 2017 where adding the Primary Key changes the behavior to not return results when converting the datetime column to datetime2 for comparison.

Meaning, SQL Server 2017 compat levels 130 and 140 behave differently than SQL Server 2019 and 2022 with the same compat levels.