SQL Date Logic

May 25, 2007

I see it all the time:  A bug caused by a query that is searching on dates in an SQL database.
Date/time logic is tricky, and deserves a little spotlight to prevent problems.

Take these two SQL statements (SQL Server syntax), for example:

  1. select * from sales
    where datediff(dd,scanDate,getdate()) between 1 and 7
  2. select * from sales
    where scanDate between dateadd(day, -7, getdate()) AND dateadd(day, -1, getdate())

At first glance, they might appear functionally equivalent.  One might even argue that query #2 is faster because SQL server can cache the result of the two dateadd function calls. Then, it can use that result for the comparison to every row of the table, while query #1 has to call datediff for every row.

But, a closer look will reveal that query #2 is factoring in time of day, and query #1 is not!

When looking at a report generated by query#2 at 9:00am expecting to see all the items sold in the previous 7 days, only items sold in the last 7 days up until 9:00am of the prior day are displayed. This is very different from the first query, which will return all the sales for all 7 of the days, regardless of the timestamp. Be sure of which results set you’re expecting and code it accordingly.

So, there you have it. Just a little Date/time logic for SQL to help prevent unnecessary chaos.