SQL Use BETWEEN to Filter Results


Example

The following examples use the Item Sales and Customers sample databases.

Note: The BETWEEN operator is inclusive.

Using the BETWEEN operator with Numbers:

SELECT * From ItemSales
WHERE Quantity BETWEEN 10 AND 17

This query will return all ItemSales records that have a quantity that is greater or equal to 10 and less than or equal to 17. The results will look like:

IdSaleDateItemIdQuantityPrice
12013-07-011001034.5
42013-07-231001534.5
52013-07-241451034.5

Using the BETWEEN operator with Date Values:

SELECT * From ItemSales
WHERE SaleDate BETWEEN '2013-07-11' AND '2013-05-24'

This query will return all ItemSales records with a SaleDate that is greater than or equal to July 11, 2013 and less than or equal to May 24, 2013.

IdSaleDateItemIdQuantityPrice
32013-07-111002034.5
42013-07-231001534.5
52013-07-241451034.5

When comparing datetime values instead of dates, you may need to convert the datetime values into a date values, or add or subtract 24 hours to get the correct results.


Using the BETWEEN operator with Text Values:

SELECT Id, FName, LName FROM Customers
WHERE LName BETWEEN 'D' AND 'L';

Live example: SQL fiddle

This query will return all customers whose name alphabetically falls between the letters 'D' and 'L'. In this case, Customer #1 and #3 will be returned. Customer #2, whose name begins with a 'M' will not be included.

IdFNameLName
1WilliamJones
3RichardDavis