Apr 072011
 

I’m not a fan of the BETWEEN operator. While the syntax is very easy to understand and requires less typing than my favorite alternative, it can lead to situations where unwanted results are returned. Because of this I’ve gotten into the habit of avoiding it all together.

If you’d like a refresher on BETWEEN, its syntax works like this:

a BETWEEN b AND c

where a, b, and c are expressions of the same data type. The result will be all values of a falling in the inclusive range between b and c. This means the following statements are equivalent:

WHERE a BETWEEN b AND c

WHERE a >= b AND a <= c

This is pretty simple to understand when you’re querying an integer column with values between 1 and 5, you would expect to find results corresponding to values in {1,2,3,4,5}. Similarly if you’re querying a datatype in the character family (CHAR, VARCHAR, etc) you’ll get just what you expect there as well. The monkey wrench gets thrown in when you start working with fractional types such as decimals, or in the case that I’d like to illustrate, DATETIME/SMALLDATETIME values.

The Problem
Let’s say you get a request that requires querying all your company’s orders from the month of March, 2011. They are conveniently stored in a table named “Orders”, which has a column of type DATETIME named “OrderDate”. You start writing your query like this:

SELECT *
FROM Orders
WHERE OrderDate BETWEEN '20110301' AND '20110331'

After some testing it’s apparent that many orders from March 31st aren’t appearing in the results as they should. In fact, only orders placed at the exact stroke of midnight (00:00:00.000) on March 31 will be included because the BETWEEN operator is only allowing dates less than or equal to March 31. The way comparisons with the DATETIME type work, any fraction of a second greater than midnight on a date is considered to be greater than that date.

“Back to the drawing board!” you say, and since you need all those orders from March 31 in your results, the next query you develop extends the date boundary by a day to ensure that all of the 31st is included:

SELECT *
FROM Orders
WHERE OrderDate BETWEEN '20110301' AND '20110401'
Parallel Parking Photo

Another BETWEEN I don’t like

April fools! This doesn’t work either, because orders placed at the stroke of midnight on April 1 will be included in the result set even though they aren’t desired. The “equal to” component that exists on both sides of BETWEEN is the culprit here. Even though the probability of an order being placed at the exact stroke of midnight is rather slim, it’s still possible, which should be reason enough to ensure it doesn’t happen rather than just crossing one’s fingers and hoping. The chances of this causing an issue are even greater when using the SMALLDATETIME data type, as its rounding methods would include any orders placed within the 29 second window from 00:00:00 to 00:00:29.

The Solution
So what’s the best easiest way to remedy this? In my mind, it’s dropping the BETWEEN and using the good ol’ comparison operators we learned in elementary school:

SELECT *
FROM Orders
WHERE OrderDate >= '20110301'
   AND OrderDate < '20110401'

This leaves little doubt that the entire month of March is being included, as all dates greater than or equal to March 1 and all dates less than April 1 are included. An equivalent query utilizing BETWEEN could be written like this:

SELECT *
FROM Orders
WHERE OrderDate BETWEEN '20110301' AND '20110331 23:59:59.997'

but I’d rather not key in all those decimals if I don’t have to. This method is also dangerous with regard to leap years. Specifying dates less than the first day of the following month accommodates leap years with no chance for error.

Other Thoughts
It should be noted that the above reasons to avoid using BETWEEN do not apply to the DATE type that was introduced in SQL Server 2008. DATE only stores whole dates, just as INT only stores whole numbers. Any of the partial date types {DATETIME, SMALLDATETIME, DATETIME2} are affected.

  4 Responses to “BETWEEN a DATE and a DATETIME Place”

  1. Another problem with BETWEEN ymd AND ymd 23:59:59.997 is if the data type of the column (or the variable/parameter if you are using parameterized statements) is SMALLDATETIME, it will round up. Also, if the column is DATETIME2, you have a slight but real chance of missing data that occurred after .997 but before midnight.

    • Excellent points, Aaron. I probably should have made rounding into an entire post by itself. I didn’t realize that about DATETIME2 either. Thanks so much!

  2. I have to deal with this a LOT, so I cast almost everything to a DATE before BETWEEN comparisons. I try to avoid the >= and <, or adding times, because it makes already complex and involved queries that much longer…

    SELECT * FROM Orders WHERE CAST(OrderDate AS DATE) BETWEEN '20110301' AND '20110331'

    • That’s definitely another way to do it. I see the simplicity you’re going for and I’m sure it works, but one downside to the query you included is that the cast operation will force a table scan so you would lose the benefit of any indexes that may be on the OrderDate column.

      Thanks for reading!

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)