BETWEEN a DATE and a DATETIME Place

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 and 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:

1SELECT *
2FROM Orders
3WHERE 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:

1SELECT *
2FROM Orders
3WHERE OrderDate BETWEEN '20110301' AND '20110401'

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:

1SELECT *
2FROM Orders
3WHERE OrderDate >= '20110301'
4   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:

1SELECT *
2FROM Orders
3WHERE 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.