Share via

BETWEEN (Transact-SQL)

  • Article

Specifies a range to test.

Topic link iconTransact-SQL Syntax Conventions

Syntax

test_expression [ NOT ] BETWEEN begin_expression AND end_expression

Arguments

  • test_expression
    Is the expression to test for in the range defined by begin_expressionand 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_expressionand begin_expression.
  • AND
    Acts as a placeholder that indicates test_expression should be within the range indicated by begin_expression and end_expression.

Remarks

To specify an exclusive range, use the greater than (>) and less than operators (<). If any input to the BETWEEN or NOT BETWEEN predicate is NULL, the result is UNKNOWN.

Result Types

Boolean

Result Value

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.

Examples

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.

See Also

Reference

> (Greater Than) (Transact-SQL)
< (Less Than) (Transact-SQL)
Expressions (Transact-SQL)
Functions (Transact-SQL)
Operators (Transact-SQL)
SELECT (Transact-SQL)
WHERE (Transact-SQL)

Other Resources

Logical Operators
Subquery Fundamentals

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

14 April 2006
New content:
  • Added a new example D that uses BETWEEN with date values.