SQL BETWEEN Examples

Problem

In this SQL tutorial we look at how to use the BETWEEN SQL operator in SQL Server along with several T-SQL examples – date values, text values, range of values, various SQL operators and more.

Solution

In this SQL database tutorial, we will provide examples of how to use SQL BETWEEN in SQL statements, subqueries, scripts and stored procedures. We will use it in the WHERE clause (filter data), with conditional IF, with CASE, INSERT, DELETE and UPDATE statements. We will use the AdventureWorks sample relational database for these examples if you want to follow along.

Simple SQL BETWEEN Operator Syntax

Here is the basic SQL BETWEEN operator syntax:

SELECT column_name
FROM schema.table_name
WHERE expression [NOT] BETWEEN Begin_Value AND End_Value -- dates, text values, etc.

The first example shows how this works. The following query shows the login ID and hire date of users from the employee table where the hire date is between the date values of January 26, 2007 and December 30, 2007.

SELECT [LoginID]
      ,[hiredate]
FROM [HumanResources].[Employee]
WHERE HireDate
BETWEEN '2007-01-26' AND '2007-12-30'

The next SQL query shows the employee pay history where the rate is between 10 and 11.

SELECT [BusinessEntityID]
      ,[RateChangeDate]
      ,[Rate]
      ,[PayFrequency]
      ,[ModifiedDate]
FROM [HumanResources].[EmployeePayHistory]
WHERE [Rate] BETWEEN 10 and 11

BETWEEN SQL Operator Date Range Example

Be careful how you use this in case the date column also includes a time stamp. For the above examples the date column just included the date, but let’s look at another example.

--create a table named #Currency3
CREATE TABLE #Currency
(
   [CurrencyCode] nchar(3) NOT NULL,
   [Name] nchar(15) NOT NULL,
   [ModifiedDate] datetime NOT NULL
)
INSERT INTO #Currency 
VALUES
  ('BTC','Bitcoin',       '2020-02-01 13:00:00'),
  ('ETC','Ehereum',       '2020-02-01 00:00:00'),
  ('ADA','Cardano',       '2020-02-01 15:30:00'),
  ('AED','Emirati Dirham','2020-02-15 13:00:00'),
  ('AFA','Afghani',       '2020-02-15 00:00:00'),
  ('ALL','Lek',           '2020-02-15 15:30:00')
SELECT *
FROM #Currency 
WHERE [ModifiedDate] BETWEEN '2020-02-01' AND '2020-02-15'

You would think this would return all 6 rows, but the following is the result set.

In order to get all rows from the SELECT statement in the specified range we need to do the following to make sure we include all of February 15th in the date range.

SELECT *
FROM #Currency 
WHERE [ModifiedDate] BETWEEN '2020-02-01' AND '2020-02-16'
-- or use something like this
SELECT *
FROM #Currency 
WHERE [ModifiedDate] BETWEEN '2020-02-01 00:00:00' AND '2020-02-15 23:59:59.999'

So be careful with dates if it also includes the time along with the date.

SQL NOT BETWEEN Example

The next example shows how to work with NOT BETWEEN, which is NOT operator syntax. The query shows the login ID and hire date of users from the employee table where the hire date is NOT between January 26, 2007 and December 30, 2007.

SELECT [LoginID]
      ,[hiredate]
FROM [HumanResources].[Employee]
WHERE HireDate NOT BETWEEN '2007-01-26' AND '2007-12-30'

BETWEEN SQL with IF Clause

The following example explains how you an use an IF clause with BETWEEN.

DECLARE @value smallint = FLOOR(RAND()*1000)
IF @value BETWEEN 0 and 500
  SELECT 'Low value' response, @value value
ELSE
  SELECT 'High value' response, @value value

The example creates a random value between 1 AND 1000, which is the given range. If the value is between 0 and 500, the value is a low value. Otherwise, the value is high.

SQL CASE with SQL BETWEEN Operator

The next example shows how to use the CASE statement combined with the BETWEEN operator. We will work with the product table of the AdventureWorks database. The example shows different values according to the List Price range. The result is returned in the [Price Information] column which uses CASE for different ranges along with the BETWEEN operator.

SELECT ProductNumber
      ,Name
      ,"Price Information" = 
         CASE   
           WHEN ListPrice BETWEEN 0 AND 100 THEN 'item - not for resale'  
           WHEN ListPrice BETWEEN 101 AND 200 THEN '10 % discount'  
           WHEN ListPrice BETWEEN 201 AND 250 THEN '20 % discount'  
           WHEN ListPrice BETWEEN 251 AND 1000  THEN 'Under $1000'  
           ELSE 'Over $1000'  
         END  
FROM Production.Product  

SQL BETWEEN vs <= and >= Logical Operators

The following example shows the BETWEEN usage for ranges.

SELECT [LoginID]
      ,[hiredate]
FROM [HumanResources].[Employee]
WHERE HireDate BETWEEN '2007-01-26' AND '2007-12-30'

The next example uses >= and <= logical operators and will display the same value.

SELECT [LoginID]
FROM [HumanResources].[Employee]
WHERE HireDate >=  '2007-01-26' AND HireDate <= '2007-12-30'

However, if you use the just < and > operators, the result set will return fewer rows. This is because the BETWEEN operator is inclusive and the more than and less than operators are not.

SELECT [LoginID]
FROM [HumanResources].[Employee]
WHERE HireDate > '2007-01-26' AND HireDate < '2007-12-30'

BETWEEN is a best practice and requires less code than using the >= and <= operators. It is easier to use and you do not need to specify the column to compare twice. For a range with a maximum and minimum value use BETWEEN whenever possible.

INSERT with SQL BETWEEN Example

Create a new temp table for this example named #Currency2 which will replicate the sales.currency data and a new table #Currency3.

SELECT *
INTO #Currency2
FROM [Sales].[Currency]
--create a table named #Currency3
CREATE TABLE #Currency3
(
   [CurrencyCode] nchar(3) NOT NULL,
   [Name] nchar(15) NOT NULL,
   [ModifiedDate] datetime NOT NULL
)

Now, insert some data into #Currency3 for a new data set.

INSERT INTO #Currency3 
VALUES
  ('BTC','Bitcoin','2020-02-01'),
  ('ETC','Ehereum','2020-03-01'),
  ('ADA','Cardano','2020-04-01')

Our example inserts the data from #Currency3 into #Currency2 using the BETWEEN operator.

INSERT INTO #Currency2
SELECT * 
FROM #Currency3 c3
WHERE c3.ModifiedDate BETWEEN '2020-02-01' AND '2020-03-01'

UPDATE with SQL BETWEEN Example

The next example will update the currency code of the data with a ModifiedDate BETWEEN January 1, 2020 and February 1, 2020.

UPDATE #Currency2
  SET [CurrencyCode] = 'BTG'
  WHERE ModifiedDate BETWEEN '2020-01-01' AND '2020-02-01'

DELETE with SQL BETWEEN Example

The next example also uses the #currency2 table. We will delete the rows from this table where the ModifiedDate is between January 1, 2020 and February 1, 2020.

DELETE FROM #Currency2
WHERE ModifiedDate BETWEEN '2020-01-01' AND '2020-02-01'

Next Steps

For more information refer to the following links:

• SQL Server IN vs EXISTS
• SQL Server SELECT Examples
• SQL NOT IN Operator
• SQL Server INNER JOIN, LEFT JOIN and RIGHT JOIN Examples
• SQL Query Language Reference Guide

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 10 years of experience as a QE and developer for SQL Server related software. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs.

• MSSQLTips Awards: Author of the Year Contender – 2015-2018, 2022, 2023 | Champion (100+ tips) – 2018