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.
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.
/* 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.
/* 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.
To learn more BETWEEN, check out these useful resources: