The SQL BETWEEN operator is a powerful tool that allows you to filter data based on a range of values. It is commonly used in SQL statements, scripts, and stored procedures to retrieve data that falls within a specified range. In this article, we will explore the syntax and usage of the SQL BETWEEN operator, along with several examples.

Basic Syntax

The basic syntax of the SQL BETWEEN operator is as follows:

expression [NOT] BETWEEN Begin_Value AND End_Value

Let’s take a look at a simple example to understand how it works:

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

In this example, we retrieve the login ID and hire date of users from the employee table where the hire date is between January 26, 2007, and December 30, 2007.

Working with Dates

When working with dates, it’s important to be careful if the date column also includes a timestamp. For example:

SELECT *
FROM #Currency
WHERE [ModifiedDate] BETWEEN '2020-02-01' AND '2020-02-15'

In this case, you might expect to retrieve all 6 rows that fall within the specified date range. However, if the date column includes a timestamp, you need to ensure that you include the entire range. To do this, you can modify the query as follows:

SELECT *
FROM #Currency
WHERE [ModifiedDate] BETWEEN '2020-02-01' AND '2020-02-16'

Alternatively, you can specify the exact timestamp range:

SELECT *
FROM #Currency
WHERE [ModifiedDate] BETWEEN '2020-02-01 00:00:00' AND '2020-02-15 23:59:59.999'

By being mindful of the timestamp, you can ensure that you retrieve the desired results.

Using NOT BETWEEN

The SQL NOT BETWEEN operator allows you to retrieve data that falls outside a specified range. For example:

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

In this example, we retrieve 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.

Conditional Statements with BETWEEN

The SQL BETWEEN operator can also be used in conjunction with conditional statements like IF and CASE. Let’s take a look at some examples:

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

In this example, we generate a random value between 1 and 1000. If the value falls between 0 and 500, it is considered a low value. Otherwise, it is considered a high value.

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

In this example, we use the CASE statement combined with the BETWEEN operator to display different values based on the list price range of products.

BETWEEN vs. Comparison Operators

The SQL BETWEEN operator is a best practice when working with ranges, as it requires less code and is easier to use compared to the >= and <= operators. For example:

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

The above query can be rewritten using the >= and <= operators:

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

However, it’s important to note that the BETWEEN operator is inclusive, while the > and < operators are not. This means that using the BETWEEN operator ensures that the maximum and minimum values are included in the range.

Using BETWEEN in DML Statements

The SQL BETWEEN operator can also be used in INSERT, UPDATE, and DELETE statements. Let’s take a look at some examples:

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

In this example, we insert data from the #Currency3 table into the #Currency2 table, filtering the rows based on the ModifiedDate column using the BETWEEN operator.

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

In this example, we update the currency code of the rows in the #Currency2 table where the ModifiedDate falls between January 1, 2020, and February 1, 2020.

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

In this example, we delete the rows from the #Currency2 table where the ModifiedDate falls between January 1, 2020, and February 1, 2020.

By utilizing the SQL BETWEEN operator in DML statements, you can easily manipulate data based on a specified range.

Conclusion

The SQL BETWEEN operator is a versatile tool that allows you to filter data based on a range of values. Whether you’re retrieving data, performing conditional statements, or manipulating data in DML statements, the BETWEEN operator provides a concise and efficient solution. By understanding its syntax and usage, you can leverage the power of the SQL BETWEEN operator in your SQL Server queries.

Thank you for reading this article. We hope you found it helpful in understanding the SQL BETWEEN operator in SQL Server.

Article Last Updated: 2022-03-09