BETWEEN (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
SQL analytics endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
Specifies a range to test.
Transact-SQL syntax conventions
Syntax
test_expression [ NOT ] BETWEEN begin_expression AND end_expression
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
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.
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.
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.
Examples
A. Using BETWEEN
The following example returns information about the database roles in a database. The first query returns all the roles. The second example uses the BETWEEN
clause to limit the roles to the specified database_id
values.
SELECT principal_id, name
FROM sys.database_principals
WHERE type = 'R';
SELECT principal_id, name
FROM sys.database_principals
WHERE type = 'R'
AND principal_id BETWEEN 16385 AND 16390;
GO
Here is the result set.
principal_id name
------------ ----
0 public
16384 db_owner
16385 db_accessadmin
16386 db_securityadmin
16387 db_ddladmin
16389 db_backupoperator
16390 db_datareader
16391 db_datawriter
16392 db_denydatareader
16393 db_denydatawriter
principal_id name
------------ ----
16385 db_accessadmin
16386 db_securityadmin
16387 db_ddladmin
16389 db_backupoperator
16390 db_datareader
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.
-- Uses AdventureWorks
SELECT e.FirstName, e.LastName, ep.Rate
FROM HumanResources.vEmployee e
JOIN HumanResources.EmployeePayHistory ep
ON e.BusinessEntityID = ep.BusinessEntityID
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
C. Using NOT BETWEEN
The following example finds all rows outside a specified range of 27
through 30
.
-- Uses AdventureWorks
SELECT e.FirstName, e.LastName, ep.Rate
FROM HumanResources.vEmployee e
JOIN HumanResources.EmployeePayHistory ep
ON e.BusinessEntityID = ep.BusinessEntityID
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 '20011212'
and '20020105'
, inclusive.
-- Uses AdventureWorks
SELECT BusinessEntityID, RateChangeDate
FROM HumanResources.EmployeePayHistory
WHERE RateChangeDate BETWEEN '20011212' AND '20020105';
Here is the result set.
BusinessEntityID RateChangeDate
----------- -----------------------
3 2001-12-12 00:00:00.000
4 2002-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 2002-01-05 would not be returned by this query because it falls outside the range.
See Also
> (Greater Than) (Transact-SQL)
< (Less Than) (Transact-SQL)
Expressions (Transact-SQL)
Built-in Functions (Transact-SQL)
Operators (Transact-SQL)
SELECT (Transact-SQL)
WHERE (Transact-SQL)