SQL BETWEEN Examples

Problem

In this SQL tutorial we look at how to use the BETWEEN operator in SQL Server along with several T-SQL examples.

Solution

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

Simple SQL BETWEEN Operator Syntax

Here is the basic SQL BETWEEN operator syntax:

expression [NOT] BETWEEN Begin_Value AND End_Value

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

SQL BETWEEN Operator Dates 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. 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'

SQL BETWEEN 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 >= 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 <= operators and will display the same value.

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

t-sql WITH less than and more than operators

However, if you use the just < and > operators, the result 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.

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:

About the author

Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-03-09

SQL BETWEEN Operator for WHERE Clause, CASE, INSERT, DELETE and UPDATE statements