Brad Corbin

SQL Server results should always be clearly-defined, right? Predictable, rational, repeatable…

Yeah, that’d be nice.

I was working with a developer investigating a query on a production server that he said wasn’t returning any results, when it ought to have returned several hundreds rows. Oddly, though, every time I ran it, it seemed to work fine. Same server, same query, same everything.

And then, suddenly, his query was returning correct results, and my query wasn’t!

No changes to the data, it was working one moment, then not working the next. Okaaayyy….

Just to try and quantify the weirdness, I sat and refreshed the query over and over. The query would return the correct result for exactly 30 seconds, then return no results at all for exactly 30 seconds. Whoa.

In most respects, it seemed like a fairly normal query, with about 5 tables in a join. The only part that seemed a little unusual to me was the following portion of the WHERE clause:

WHERE GETDATE() BETWEEN CATEGORY.FROM_DATE
AND ISNULL(CATEGORY.TO_DATE, GETDATE())

Looks a little unusual, but the logic seems sound. Its designed to return all rows that are currently active, ie where the FROM_DATE is in the past, and the TO_DATE is still in the future (or TO_DATE is NULL, indicating no expiration date has been set). In this case, all rows should have been retuned, because nothing had an expiration date yet. I probably wouldn’t have designed the query that way, but this logic seemed to work properly when I tested it against several sample tables.

The final clue was that this logic seemed to work fine on two of the tables in the join, but when it was used on a third table, we’d see the weird “on/off” behavior.

If you haven’t already guessed, the problem with that third table turned out to be that the TO_DATE field was a smalldatetime instead of a normal datetime. This meant that the ISNULL did an explicit conversion of GETDATE() to smalldatetime, which only has accuracy to the nearest minute.

And when you do a Convert(smalldatetime, GetDate()), half the time it will round down (first 30 seconds), and half the time it will round up (last 30 seconds of each minute). Here’s a query you can run that demonstrates this:

SELECT getdate() as Normal,
       convert(smalldatetime,getdate()) as Small,
       Case WHEN (getdate() <= convert(smalldatetime,getdate())) THEN 'True'
            ELSE 'False'
       END as DateIsBetween

So how to fix this? Well, we could of course simply change the datatype of the TO_DATE column in the third table, but I recommended to the developer that he alter the logic to something like:

WHERE (TO_DATE IS NULL AND GETDATE() >= FROM_DATE)
   OR (TO_DATE IS NOT NULL AND GETDATE() BETWEEN FROM_DATE AND TO_DATE)

which avoids the ISNULL function altogether.

Photo courtesy of “picadillywilson” and Creative Commons. See his flickr photostream here.