We have been running SQL Server 2016 for some time now but initially left the database compatibility level to SQL Server 2008 (100). Recently we did update the DB Compatibility Level to 2016 (130) but we have encountered few issues there. We managed to find one particular one where a query converting datetime to datetime2 and it returns NULL when it should have returned a value, not sure if it is CE or something? Please see the example below:
CREATE TABLE TestCE
(
Id INT IDENTITY(1,1),
CurrentDateTime DATETIME
)
GO
INSERT dbo.TestCE(CURRENTDATETIME)
SELECT GETDATE()
UNION ALL SELECT '2018-04-11 08:44:42.643'
UNION ALL SELECT '2018-04-12 09:49:45.334'
GO
SELECT * FROM TestCE
--The resultset
1 2018-04-17 16:49:02.813
2 2018-04-11 08:44:42.643
3 2018-04-12 09:49:45.333
But when I run the following queries, don't get any result:
SELECT * FROM TESTCE
WHERE CURRENTDATETIME = CONVERT(DATETIME2, '2018-04-12 09:49:45.333')
GO
SELECT * FROM TESTCE
WHERE CURRENTDATETIME = CONVERT(DATETIME2, '2018-04-11 08:44:42.643')
GO
SELECT * FROM TESTCE
WHERE CURRENTDATETIME = CONVERT(DATETIME2, '2018-04-17 16:49:02.813')
I have also tried with QueryTraceOn option for backward CE but with no success:
SELECT * FROM TESTCE
WHERE CURRENTDATETIME = CONVERT(DATETIME2, '2018-04-12 09:49:45.333')
OPTION(QUERYTRACEON 9481);
GO
SELECT * FROM TESTCE
WHERE CURRENTDATETIME = CONVERT(DATETIME2, '2018-04-11 08:44:42.643')
OPTION(QUERYTRACEON 9481);
GO
SELECT * FROM TESTCE
WHERE CURRENTDATETIME = CONVERT(DATETIME2, '2018-04-17 16:49:02.813')
OPTION(QUERYTRACEON 9481);
Also set the following DB option but with no success either:
ALTER DATABASE SCOPED CONFIGURATION
SET LEGACY_CARDINALITY_ESTIMATION = ON
As soon as I change the compatibility level back to 2008, queries start returning the data as expected.