Puzzle – Datatime to DateTime2 Conversation in SQL Server 2017

Earlier last week, I was presenting various questions about SQL Server 2017 at PASS in Seattle. However, during one of the session, I was fortunate to meet SQL Server Expert Christopher Savage. He shared a very interesting puzzle with me. I have been trying to solve the puzzle for a while, but I have no answer for it. Let us see the puzzle about DateTime2 Conversion in SQL Server 2017.

Let us see a quick demonstration. Please note that this puzzle will work on only SQL Server 2017 compatibility level. If you are running your database on any earlier compatibility level, it will not work.

First, let us create a database and set up the compatibility level to SQL Server 2017.

CREATE DATABASE TestDB
GO
ALTER DATABASE [TestDB] SET
COMPATIBILITY_LEVEL = 140 -- SQL Server 2017
GO

Next, run the following code. As I have specified GO 10 at the end of the query, it will run the query 10 times.

DECLARE @now DATETIME = GETDATE();
SELECT CASE
WHEN CAST(@now AS DATETIME2) = @now THEN 1
ELSE 0 END, CAST(@now AS DATETIME2) as DT2, @now as DT
GO 10

When you run the query, you will see the image which is similar to the following image.

You can see in the result we have results column changing the value between 1 and 0. Here is the puzzle for you.

Puzzle: Why does SQL Server change a value from 0 and 1 while we are always comparing the datetime in different data types.

Reference: Pinal Dave (https://blog.sqlauthority.com)