SQL BETWEEN Operator

Use the SQL BETWEEN to test whether a column value is within a range of specified values. You can use the SQL BETWEEN operator with a variety of types, such as integer, varchar, and dates.

Person Table used for SQL BETWEEN example

The following SQL shows how to use BETWEEN to find all people having last names greater than or equal to Colvin and less than or equal to Conteras.

SELECT FirstName, LastName FROM Person.Person WHERE LastName BETWEEN 'Colvin' and 'Contreras' 
/* Answer */
SELECT FirstName, LastName
FROM Person.Person
WHERE LastName BETWEEN 'Colvin' and 'Contreras'

Using BETWEEN improves the readability of your SQL. Notice it eliminates several comparison operators. The above example is equivalent to:

SELECT FirstName, LastName
FROM Person.Person
WHERE LastName >= 'Colvin' AND
LastName <= 'Contreras'

SQL BETWEEN Operator with Dates

SQL BETWEEN is well suited for date range comparisons since it help improves your SQL’s readability.

It this example see how BETWEEN simplifies selecting every Person whose record was between January 12th – 14th, 2014 date range.

SELECT FirstName, LastName, ModifiedDate FROM Person.Person WHERE ModifiedDate BETWEEN '2014-01-12' AND '2014-01-14' 
/* Answer */
SELECT FirstName, LastName, ModifiedDate
FROM Person.Person
WHERE ModifiedDate BETWEEN '2014-01-12' AND '2014-01-14'

In summary, use the SQL BETWEEN operator to test against a range of values.

The range is inclusive.

Using SQL BETWEEN is shorthand for using >= AND <= conditionals.

Additional Resources

To learn more BETWEEN, check out these useful resources:

>