BETWEEN (Transact-SQL)
Specifies a range to test.
Transact-SQL Syntax Conventions
- test_expression
-
Is the expression to test for in the range defined by begin_expression and end_expression. test_expression must be the same data type as both begin_expression and end_expression.
- NOT
-
Specifies that the result of the predicate be negated.
- begin_expression
-
Is any valid expression. begin_expression must be the same data type as both test_expression and end_expression.
- end_expression
-
Is any valid expression. end_expression must be the same data type as both test_expression and begin_expression.
- AND
-
Acts as a placeholder that indicates test_expression should be within the range indicated by begin_expression and end_expression.
BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.
NOT BETWEEN returns TRUE if the value of test_expression is less than the value of begin_expression or greater than the value of end_expression.
A. Using BETWEEN
The following example returns the employees of Adventure Works Cycles that have an hourly pay rate between 27 and 30.
USE AdventureWorks;
GO
SELECT e.FirstName, e.LastName, ep.Rate
FROM HumanResources.vEmployee e
JOIN HumanResources.EmployeePayHistory ep
ON e.EmployeeID = ep.EmployeeID
WHERE ep.Rate BETWEEN 27 AND 30
ORDER BY ep.Rate;
GO
Here is the result set.
FirstName LastName Rate ----------- ------------------ ------------------ Paula Barreto de Mattos 27.1394 Janaina Bueno 27.4038 Dan Bacon 27.4038 Ramesh Meyyappan 27.4038 Karen Berg 27.4038 David Bradley 28.7500 Hazem Abolrous 28.8462 Ovidiu Cracium 28.8462 Rob Walters 29.8462 Sheela Word 30.0000 (10 row(s) affected)
B. Using > and < instead of BETWEEN
The following example uses greater than (>) and less than (<) operators and, because these operators are not inclusive, returns nine rows instead of ten that were returned in the previous example.
USE AdventureWorks;
GO
SELECT e.FirstName, e.LastName, ep.Rate
FROM HumanResources.vEmployee e
JOIN HumanResources.EmployeePayHistory ep
ON e.EmployeeID = ep.EmployeeID
WHERE ep.Rate > 27 AND ep.Rate < 30
ORDER BY ep.Rate;
GO
Here is the result set.
FirstName LastName Rate --------- ------------------- --------- Paula Barreto de Mattos 27.1394 Janaina Bueno 27.4038 Dan Bacon 27.4038 Ramesh Meyyappan 27.4038 Karen Berg 27.4038 David Bradley 28.7500 Hazem Abolrous 28.8462 Ovidiu Cracium 28.8462 Rob Walters 29.8462 (9 row(s) affected)
C. Using NOT BETWEEN
The following example finds all rows outside a specified range of 27 through 30.
USE AdventureWorks;
GO
SELECT e.FirstName, e.LastName, ep.Rate
FROM HumanResources.vEmployee e
JOIN HumanResources.EmployeePayHistory ep
ON e.EmployeeID = ep.EmployeeID
WHERE ep.Rate NOT BETWEEN 27 AND 30
ORDER BY ep.Rate;
GO
D. Using BETWEEN with datetime values
The following example retrieves rows in which datetime values are between '19971212' and '19980105', inclusive.
USE AdventureWorks GO SELECT EmployeeID, RateChangeDate FROM HumanResources.EmployeePayHistory WHERE RateChangeDate BETWEEN '19971212' AND '19980105'
Here is the result set.
EmployeeID RateChangeDate
----------- -----------------------
3 1997-12-12 00:00:00.000
4 1998-01-05 00:00:00.000
The query retrieves the expected rows because the date values in the query and the datetime values stored in the RateChangeDate column have been specified without the time part of the date. When the time part is unspecified, it defaults to 12:00 A.M. Note that a row that contains a time part that is after 12:00 A.M. on 1998-0105 would not be returned by this query because it falls outside the range.
