The problem at hand

At first, this one was very peculiar. The same query giving different results when executed multiple times in a row. Sure, that can happen because data is changing in the background. But that was not the case since nobody else was working on that database.

The Query was pretty straightforward, nothing special about it. To illustrate the case, I isolated the part that was most likely the problem:

declare @ThisDate datetime2 = null

select
case
when isnull(@ThisDate, getdate()) = getdate() then 'Yes'
else 'No'
end as TheyAreEqual
GO 10

As you can see there is a Variable @ThisDate which I deliberatly left empty, so Null. This forces the IsNull() function to return getdate(). So basically I compare getdate() with getdate() which obviously should always be true, right?

WRONG!

As you will see at the bottom I put GO 10 which will take care the Query gets executed 10 times. Now if you run this Query, watch the results:

Now if you run the same Query again, the Yes and No's will differ, but you will never get a consistent, predictable result. And a Query that is unpredictable is of course: unacceptable.

The cause of the problem

The IsNull() should return a getdate() which should be equal to the getdate(), but it looks like they are not. Well, at least not all of the time which is what we assume but clearly does not happen.

It looks like our assumption that getdate() returns the same in both cases is wrong. So let's expand the Query and show the results on which we do the compare:

declare @ThisDate datetime2 = null

;with Dates as (
  select
  isnull(@ThisDate, getdate()) as ThisDate,
  getdate() as TheDate
)
select *,
case
  when ThisDate = TheDate then 'Yes'
  else 'No'
end as ThisDateIsEqual
from Dates
GO 10

Now by isolating the problem and looking at the actual values for the comparison, we can see what actually happens:

Clearly ThisDate, the getdate() inside the IsNull() Funtion, is not the same as TheDate from the direct getdate() Function. The difference is in the precision. If you are lucky, the more precise version is exaclty the same as the less precise version. But when there is even the slightest rounding applied, they are not!

Apparently, the getdate() Function returns a DateTime DataType when executed directly. But it returns a DateTime2 DataType here in the IsNUll() Function, or does it? Let's expand the Query even further to see how the IsNull() Function behaves:

declare @ThisDate datetime2 = null
declare @ThatDate datetime = null

;with Dates as (
  select
  isnull(@ThisDate, getdate()) as ThisDate,
  isnull(@ThatDate, getdate()) as ThatDate,
  getdate() as TheDate
)
select *,
case
  when ThatDate = TheDate then 'Yes'
  else 'No'
end as ThisDateIsEqual
from Dates
GO 10

Here are the results:

Aha, so it turns out that the IsNull() Function is smarter than expected. By default the getdate() Function returns a DateTime DataType. But the IsNull() Function takes into consideration what the DataType of the Check Value is. And since @ThisDate is a DateTime2 DataType, the IsNull() Function forces the getdate() to return just that, a DateTime2 DataType. With the @ThatDate which is a DateTime DataType, we get that DataType.

That is why the DataTypes are slightly different. Sometimes, the rounded precision is the same as the not-rounded precision, in which case the are equal. In other cases, they are not.

So how to solve this?

Now we know what caused the problem, we also know how to solve it. We need to take care that we compare exactly the same, not roughly the same.

For that we have two options. First one is to force the IsNull() Function to return a DateTime DataType by making sure the Check Value is of that DataType. That way we compare a DateTime with a DateTime. Or we could cast the getdate() on the right side to a DateTime2 DataType. That way we compare a DateTime2 with a DateTime2:

declare @ThisDate datetime2 = null

select
case
when isnull(cast(@ThisDate as datetime), getdate()) = getdate() then 'Yes'
else 'No'
end as TheyAreEqual,
case
when isnull(@ThisDate, getdate()) = cast(getdate() as DateTime2) then 'Yes'
else 'No'
end as TheyAreEqualToo
GO 10

What have we learned

Although the same Function was used, the results may differ because of how it was used. The best way to check that is to isolate the problem and then display the values in the result.

By assuming that the result must be the same, you might overlook the fact they aren't. Displaying them in seperate columns, will prove if the assumption is actually true or not.

That's it for today!

Well, this was one curious case that took me some time to figure out. That's why I used it as an example, so you don't have!

And to demonstrate why isolating the problem and looking at the seperate values will always give you the answer you need to solve the problem. And as thet say: assumption is the mother of all fuck-ups. Or Assume makes an ASS of U and ME... 😉

Remember this the next time you encounter a problem you cannot explain straight away!

You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!