SQL BETWEEN-Smart Tips to Scan for a Range of Values

SQL BETWEEN is an operator used to specify a range of values to test. The returned value can be inclusive or within the range. Or it can be outside of the range if you add the NOT operator before it. It works for dates, dates with time, numbers, and strings.

You can use it on WHERE clauses for the following:

• SELECT,
• INSERT (with SELECT)
• UPDATE,
• and DELETE.

It also works for HAVING clauses together with GROUP BY.

But if you’re not careful, SQL BETWEEN can make you nuts when using it, especially with dates with time.

Not to worry, though. We have examples to deal with the gotchas in using SQL BETWEEN. But before that, the sample data I used came from NOAA. You can request weather data for free from them. I used the hourly temperature records for the United States in the year 2010. Then, I imported the CSV data to the SQL Server using SQL Server Management Studio. I renamed the columns and added a non-clustered index.

Let’s begin.

Using SQL BETWEEN with Dates and Times

This must be the most searched item when dealing with SQL BETWEEN. We will use examples to explain how it works.

Tip #1: For DATETIME Columns, Specify Both the Date and Time

WRONG USAGE

Let’s start with the wrong usage to stress this point. The following use of BETWEEN with DATETIME columns will give unexpected results.

SELECT 
 DateHour
,Hourly_Heating_Degree_Hours
FROM TemperatureData
WHERE DateHour BETWEEN '01/01/2010' AND '01/02/2010'
AND Latitude = 41.995
AND Longitude = -87.9336;

The query returns data for 2 days from a weather station near the O’Hare International Airport in Chicago. You can notice the range between a lower value (01/01/2010) and a higher value (01/02/2010). Here’s the result set in Figure 1.

Figure 1. Result set of a query using SQL BETWEEN 2 dates.

But where’s the problem?

It’s supposed to be an hourly record for 2 days. Because of that, the result set should have 48 records. But notice it’s only 24. The problem lies with the time element of the DateHour column. When you don’t specify the time in a DATETIME column, it assumes 00:00 or 12:00 AM. Also, note that the data started on January 1, 2010, at 01:00 AM, not 12:00 AM.

So, internally, SQL Server used DateHour BETWEEN ’01/01/2010 00:00:00.000′ AND ’01/02/2010 00:00:00.000′. How do we know?

THE DATE IS ACTUALLY A STRING

That’s right.

The date values enclosed in single quotes are not really dates but strings. SQL Server uses implicit conversion to convert the string to DATETIME. After the conversion, the time portion will be appended to the date.

Let’s inspect with Include Actual Execution Plan. Press Ctrl-M in SQL Server Management Studio, then re-run the previous example.

When the Execution Plan appears, right-click the Index Seek operator and select Properties. See Figure 2.

Figure 2. Implicit conversion of a string to DATETIME. It is hidden in the Execution Plan of a query using BETWEEN.

Then expand the Seek Predicates. The boxed portions of Figure 2 show the implicit conversion of the 2 strings to DATETIME. Since implicit conversion is done internally, newbies get confused why their expectations in the result set are not met.

CORRECT USAGE

The example below will return the hourly records between 8:00 AM and 12:00 PM on January 2, 2010.

SELECT * FROM TemperatureData
WHERE DateHour BETWEEN '01/02/2010 08:00' AND '01/02/2010 12:00'
AND Latitude = 41.995
AND Longitude = -87.9336;

You need to specify the time portion, especially when dates are the same. Or your expected results will not happen.

To return the records for the whole day, this won’t work:

SELECT 
 DateHour
,Hourly_Heating_Degree_Hours
FROM TemperatureData
WHERE DateHour = '06/01/2010'
AND Latitude = 41.995
AND Longitude = -87.9336;

It will only return 1 record – the one for June 1, 2010, at 12:00 AM. But using BETWEEN with the specified times, you can return each hour’s record for the whole day. See the next example.

SELECT 
 DateHour
,Hourly_Heating_Degree_Hours
FROM TemperatureData
WHERE DateHour BETWEEN '06/01/2010 00:00' AND '06/01/2010 23:00'
AND Latitude = 41.995
AND Longitude = -87.9336;

Note that I specified up to 23:00 only. If your data uses any time of the day, use 23:59 or 11:59 PM in the higher value of the range. Specify the seconds also if you need that.

Tip #2: Consider the DATE Data Type

If you don’t need the time portion, consider the DATE data type instead. And you will avoid the trouble mentioned above.

SQL BETWEEN with Numbers

Let’s move on to numbers.

Tip #3: Include the Decimal Portion for Non-Integer Values

SELECT
 DateHour
,[Hourly_Heating_Degree_Hours]
FROM TemperatureData
WHERE DateHour BETWEEN '06/01/2010' AND '06/5/2010 23:00'
AND [Hourly_Heating_Degree_Hours] BETWEEN 5.0 AND 7.0
AND Latitude = 41.995
AND Longitude = -87.9336;

Note the addition of another condition involving numbers. The results will further be limited to 5 and 7 degrees.

When using DECIMAL, MONEY, or FLOAT data types, specify the decimal portion even if it’s zero, like 52.00 or 10.0000. This way, you avoid implicit conversion to the target DECIMAL, MONEY, or FLOAT data types.

SQL BETWEEN with Strings

Tip #4: For Strings, Range is Based on Collation

With strings, BETWEEN evaluates values based on alphabetical order. ‘A’ is the least and ‘Z’ is the greatest. You can also say that in general, evaluation is based on collation. Because English is not the only language SQL Server supports. Collation provides sorting rules, case, and accent sensitivity. Let’s use the AdventureWorks database for this example. Check out the code below and the result in Figure 3.

USE AdventureWorks
GO

SELECT 
 LastName
,FirstName
,MiddleName
FROM Person.Person
WHERE Lastname BETWEEN 'Spanaway' AND 'Splane'
ORDER BY LastName;

Figure 3. Result set of a query using BETWEEN with strings.

The range covers the last name Spanaway. But where is Splane? It’s non-existent in the database. So, the result only reached up to Spicer.

SQL BETWEEN Tips for All Supported Data Types

Whether you’re using BETWEEN for dates, numbers, or strings, there are common things you should be aware of. This could be common sense, but it still happens by mistake. Read on how this can happen.

Tip #5: Both Start and End Values Can’t be NULL

BETWEEN needs start and end values for the range. Each should have a value that is not NULL. There is an example with a NULL end value below.

SELECT 
 DateHour
,Hourly_Heating_Degree_Hours
FROM TemperatureData
WHERE DateHour BETWEEN '01/01/2010' AND NULL;

This can happen if you call the SELECT statement from an app or a stored procedure, and you didn’t validate it properly.

Tip #6: The Start Value Can’t be Greater Than End Value

Nothing will also be returned if both values are not NULL, but the range is reversed. Here’s an example.

SELECT 
 DateHour
,Hourly_Heating_Degree_Hours
FROM TemperatureData
WHERE DateHour BETWEEN '01/30/2010' AND '01/01/2010';

Aside from dates, the following expressions won’t return a result too:

• value BETWEEN 100 AND -200. Because -200 is lower than 100.
• work BETWEEN ‘Zookeeper’ AND ‘Accountant’. Because ‘Z’ is greater than ‘A’.

Tip #7: Range Values Should be the Same Data Types

Sometimes, user interface controls have unexpected output. Or we just picked up the wrong property. And if we don’t check it before passing it to SQL Server, a situation like this might happen:

SELECT 
 DateHour
,Hourly_Heating_Degree_Hours
FROM TemperatureData
WHERE DateHour BETWEEN '06/01/2010' AND 'Saturday, June 5, 2010'
AND Latitude = 41.995
AND Longitude = -87.9336;

A conversion error from a character string to a date will occur.

So, the lesson from Tip #5 to #7 is to validate the start and end values of the range.

Tip #8: Use NOT BETWEEN to Exclude Values

Consider another example.

SELECT
 MONTH(DateHour) AS [Month] 
,round(AVG([Hourly_Heating_Degree_Hours]),2) AS AverageTemperature
FROM TemperatureData
WHERE DateHour BETWEEN '01/01/2010 00:00' AND '06/30/2010 23:00'
AND DateHour NOT BETWEEN '05/01/2010 00:00' AND '05/31/2010 23:00'
AND Latitude = 41.995
AND Longitude = -87.9336
GROUP BY MONTH(DateHour);

This will return the monthly average from January to June but will exclude May. Excluding the records for May 2010 is made possible by NOT BETWEEN. Here’s the result set in Figure 4.

Figure 4. Result set of a query using NOT BETWEEN.

SQL BETWEEN Compared to Other Operators

Tip #9: Use IN if You Need a List and Not a Range

The IN operator determines whether a value matches any value in a list or subquery. Meanwhile, using NOT IN checks whether a value does not match.

Both BETWEEN and IN operators filter data based on multiple values. But the difference lies in the set of values being matched. BETWEEN uses a range. But IN uses comma-separated values in a list or rows in a subquery.

Check the example below.

SELECT
 DateHour
,[Hourly_Heating_Degree_Hours]
FROM TemperatureData
WHERE DateHour BETWEEN '06/01/2010' AND '06/5/2010 23:00'
AND [Hourly_Heating_Degree_Hours] IN (5.2, 6, 7, 3.7)
AND Latitude = 41.995
AND Longitude = -87.9336;

Look at the list of values used by IN. It doesn’t need to be a list of increasing values. The last value in the list (3.7) is also the least among the numbers.

Tip #10: Choose from BETWEEN or >= with <=

At runtime, SQL Server converts BETWEEN to >= with <= operators. How do we know?

Look at the code below.

SELECT
 DateHour
,AVG(Hourly_Heating_Degree_Hours) AS AverageTemp
FROM TemperatureData
WHERE DateHour BETWEEN '01/01/2010 08:00' AND '01/01/2010 12:00'
GROUP BY DateHour;

SELECT
 DateHour
,AVG(Hourly_Heating_Degree_Hours) AS AverageTemp
FROM TemperatureData
WHERE DateHour >= '01/01/2010 08:00' 
AND DateHour <= '01/01/2010 12:00'
GROUP BY DateHour;

Both queries will have the same result set as the one in Figure 5.

Figure 5. Result set in using either BETWEEN or >= with <=.

They also have the same execution plan, as seen in Figure 6.

Figure 6. Execution plan of 2 queries comparing the use of BETWEEN, and >= and <= operators.

But here’s the thing.

Notice the first Index Seek operator in Figure 6. Then, see the Seek Predicates. Do you see the BETWEEN keyword? There’s none, right? Because it is converted to >= with <= operators. Those are the operators present in the Seek Predicates.

But there’s more.

If you hover your mouse to the second Index Seek operator, you will see the same properties as the first Index Seek.

So, it seems that the BETWEEN operator is a shortcut to >= with <= operators. You’ll type more if you use the latter. You will see the same conversion happen when BETWEEN is used in numbers and strings.

In the end, it’s up to you if you use BETWEEN or the >= and <= operators. The conversion time it takes to convert BETWEEN is negligible. But if you still don’t want that extra, negligible time, use >= and <= operators.

Bottomline

SQL BETWEEN is good for fetching data inclusive of the range. And it’s not that hard to use. Even the DATETIME values are manageable with BETWEEN. Just make sure you cover the time portion properly. It is also equivalent to using >= with <=. It’s up to you which you prefer to use.

You can bookmark this page to get SQL BETWEEN tips for dates, numbers, and strings when you need them.

If you have some tricks using BETWEEN that we didn’t cover, you can share them with us in the Comments section. And if you like this article, please share it by pressing social media buttons.

Happy coding, everyone!

Tags: statements, tips and tricks Last modified: September 16, 2021