Whenever we talk about inclusive date comparison & filtering in T-SQL, the only two options that come to mind are the BETWEEN clause and the greater than (>) and less than (<) operators paired with the equal to operator. Today, let us attempt to find out if there is a difference between the two.
Let me mention that our study today will be restricted to dates only. DATETIME values need extra “precautions” on the part of the query developer to cover the time portion in the comparison. However, most legacy systems (and as we would see from the examples in the AdventureWorks2008R2 database), use separate storage for date and time values and therefore, the need to filter on the date value is a very realistic user scenario.
Dissecting BETWEEN
Without spending much time, let’s get right along to the simple test. Let’s assume that once a year, the Adventure Works Bicycle Company prepares a list of all employees who were hired in the organization 10 years ago, (because we are in 2011, this would be in 2001) to send out an organization-wide recognition of service notification. Quite simply, the query would be something like:
SELECT * FROM HumanResources.Employee WHERE HireDate BETWEEN '2001-01-01' AND '2001-12-31' GO
We get 2 records in return:
Now, let us study the actual execution plan to find out what goes on under the covers.
As it turns out, BETWEEN is internally implemented by pairing a set of comparison operators with the equal to operator!
Conclusion
We can safely conclude that BETWEEN is just a facade for the comparison operators. The only difference is in the fact that comparison operators, by themselves offer exclusive comparison (i.e. the comparison excludes the value being compared to). For an inclusive comparison, they need to be paired with the equal to (=) operator – something which the BETWEEN does out-of-the-box.
So is BETWEEN really required?
I studied the Books-On-Line pages for BETWEEN and the Comparison operators, and could not find any difference other than the one mentioned above (about BETWEEN being inclusive). So, the big question now becomes whether BETWEEN is really required? Wouldn’t our code be much better if we avoid the need for Microsoft SQL Server to transform the between into comparison operators?
Well, in my humble opinion, that’s where the issue is. We, as developers and ultimately, humans, tend to make mistakes. The compilers warn us of syntactical issues with our code, but there is nothing that would warn us about a logical bug. A single miss in placing an equal to operator (or placing of an operator in excess), might result in code that would be logically incorrect. Using BETWEEN when we need inclusive comparison just helps in avoiding such errors.
So, BETWEEN is required – it’s not mandatory, but recommended. And that’s why it’s a T-SQL coding best practice.
Interested in more such surprises?
If you are interested in more such surprises, please do check out my post on the differences between CAST & CONVERT here: http://beyondrelational.com/blogs/nakul/archive/2011/07/18/cast-v-s-convert-is-there-a-difference-as-far-as-sql-server-is-concerned-which-is-better.aspx.
Until we meet next time,
Be courteous. Drive responsibly.
SQLTwins by Nakul Vachhrajani 
Good post. But note that COL BETWEEN @a and @b is more readable and understandable than COL>=@a and COL<=@b. It is becuase BETWEEN is plain english and easy to understand. Also you dont need to repeat the column twice for the comaprison. Also if any of the operators is missed or interpreted wrongly, the result is not accurate.
But I prefer using >= and <=. It is becuase it is very useful where compare datetime values. You need to specify time part when you use BETWEEN.
LikeLike
Exactly my point, Madhivanan when I attempt to answer the question whether BETWEEN is really required.
Thank-you for your valuable comments. It is good to know that I am not the only one who gets nervous around people using comparison operators without reviewing their code and ultimately resulting in logical bugs in the application.
LikeLike
Here are more good examples of why you should neve, EVER use BETWEEN when searching for date ranges… and some GOOD ALTERNATIVES with examples.
Click to access ComparingDatesInSQL.pdf
TheSQLWhisperer
LikeLike
@SQL WHisperer: As I mentioned, the time component will need additional precautions on the part of the developer – there is absolutely no doubt about that. The white paper you point out points out the issues that might occur in detail. It’s a good one, and thank-you for sharing.
However, in most legacy systems, the date and time components are stored separately (and apparently, the need to do still exists because the new DATE type was just introduced). In such cases, the BETWEEN is a much safer alternate.
BETWEEN has it’s uses, and therefore, like almost everything on this planet needs to be used with proper judgement and care.
LikeLike
For my testing I found converting the target column to DATE for time insensitive comparison performed over 55k rows actually performed better than using full DateTime with filter values inclusive of the time needed, and I find both to be accurate. I did get a good laugh about the idea of comparison based on converting the date to a string…
SELECT *
FROM HumanResources.Employee
WHERE convert(date, HireDate) BETWEEN ‘2001-01-01’ AND ‘2001-12-31’;
Verses
SELECT *
FROM HumanResources.Employee
WHERE HireDate BETWEEN ‘2001-01-01 00:00:00.000’ AND ‘2001-12-31 23:59:59.997’;
LikeLike
Just a heads-up… converting dates to strings, and then comparing the strings uses a a LOT of resources, especially if enough queries are pounding your database and cycling through your cache quickly. Take advantage of your execution plan by NOT comparing strings. Again, take a minute and read this article. It’s based on many lessons learned, and full of good (free) info.
Click to access ComparingDatesInSQL.pdf
TheSQLWhisperer
LikeLike
@SQL Whisperer: I agree. Conversion to strings is a bad idea here.
In fact, unless absolutely necessary, I try never to convert to strings for the same reason – it takes a lot of resources.
LikeLike
Hi guys,
I have seen freshers assuming that comparison value order does not matter, but that is not the case.
Always smaller value should be first followed by larger value
This confusion arises due to English meaning of between.
Agree that this situation will not come with an experienced person.
To avoid this, best way is if we can write the code using >= and <=
Thanks
Jeetendra
LikeLike
FYI:The current ANSI syntax is:
::=
[NOT] BETWEEN [ASYMMETRIC | SYMMETRIC]
AND
The ASYMMETRIC option is the default and it is the curent behavior. The SYMMETRIC opton swaps the two s so it tests “in both directions” at once. ACCESS had this as a dialect and we considered it at one time in ANSI X3H2
LikeLike