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 SQL database in Microsoft Fabric

Specifies a range to test.

Transact-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.

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's 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's 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's 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)