T-SQL 101: 24 Selecting ranges of values with the T-SQL BETWEEN operator in SQL Server

The T-SQL BETWEEN operator let’s you specify a range of values. So in this case shown here:

I’m asking for rows where the DateCreated is between the first date and the second date. Notice how I’ve written the date here. We’ll talk about that a bit more in a later post. 2012, zero one, twenty-one is the 21st of January 2012. This is one of the formats that makes dates consistent.

The key thing to understand with a T-SQL BETWEEN operator though is that it’s not like the word “between” in the English language. If I’m speaking English and I ask for a location between “here” and “there”, I usually am not including the end points “here” and “there”, only the points in between them.

In T-SQL however, BETWEEN includes the start and end values. So if I have an integer and say BETWEEN 4 and 6, I mean the values 4, 5, and 6.

But be careful !

The reason that I mentioned dates here is that they can be a challenge. A really common mistake is to have a datetime value (or a datetime2 value) that includes both a date and a time, and to then just put the dates in the BETWEEN.

For that reason, I have friends who just don’t use the BETWEEN operator at all. Instead, they would rather change the statement to two predicates, one where DateCreated is greater than or equal to 20120121 and another one where DateCreated is less than 20120125.

I’m actually quite comfortable in many cases with using BETWEEN, as I think it more clearly expresses the intent. It’s cleaner and more expressive to have a single predicate to explain that a value should be from 4 to 6, rather than two separate predicates. But as soon as you start having time values involved, I think you do need to avoid using BETWEEN.

Regardless, BETWEEN is part of the T-SQL language, so you need to understand how it works.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2019-07-01