Wrong result comparing GETDATE() to stored GETDATE() in SQL Server

Question

I would expect the result to always be 1:

DECLARE @x datetime2
SELECT @x=GETDATE()
SELECT CASE WHEN @x>GETDATE() THEN 0 ELSE 1 END

But it is sometimes 1 and sometimes 0. How is that possible?

Answer 1

It takes time for code to execute. Sometimes a tick happens in between lines two and three, and sometimes (usually) not. In the latter case (no tick), @x is still equal to (not greater than) the GETDATE() value in the third line and you end up with 0. What surprised me at first is you ever see 1. When a tick does happen, @x should now be less than the new GETDATE() value in line 3 and you'd still see 0.

But it makes more sense when you run this code:

DECLARE @x datetime2
SELECT @x=GETDATE()
SELECT @x, GETDATE(), CASE WHEN @x>GETDATE() THEN 0 ELSE 1 END

Now we can see what's going on better. Here's a sample result on my machine:

2018-01-19 23:32:21.3833333   |  2018-01-19 23:32:21.383   |    1

Ahhh... @x is a datetime2 with more precision than the older datetime used by GETDATE(). And you can see in the documentation for GETDATE() that it does return a datetime rather than datetime2. So we have some rounding error going on between the two values.

For the 0 values, I ran the altered code 30 or 40 times (hitting F5 for refresh), and ALL of the 0's I saw looked like this:

2018-01-19 23:36:29.0366667   |   2018-01-19 23:36:29.037   |   0

Where the last digit in the second column was a 7 and the first column had repeating 6's with a rounded 7 at the end.

One thing still confuses me. The GETDATE() function returns a datetime value, but somehow it assigns datetime2 precision to @x. I'd expect to just see extra zeros that always match the original GETDATE() result.