Export (0) Print
Expand All
ABS
ALL
AND
ANY
AVG
bit
COS
COT
DAY
EXP
GO
IN
LEN
LOG
MAX
MIN
NOT
OR
PI
SET
SIN
STR
SUM
TAN
USE
VAR
Expand Minimize
12 out of 22 rated this helpful - Rate this topic

BETWEEN

SQL Server 2000

Specifies a range to test.

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_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 Microsoft® SQL Server™ expression. begin_expression must be the same data type as both test_expression and end_expression.

end_expression

Is any valid SQL Server expression. end_expression must be the same data type as both test_expression and begin_expression.

AND

Acts as a placeholder indicating that 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. Use BETWEEN

This example returns title identifiers for books with year-to-date unit sales from 4,095 through 12,000.

USE pubs
GO
SELECT title_id, ytd_sales
FROM titles
WHERE ytd_sales BETWEEN 4095 AND 12000
GO

Here is the result set:

title_id ytd_sales 
-------- ----------- 
BU1032   4095        
BU7832   4095        
PC1035   8780        
PC8888   4095        
TC7777   4095        

(5 row(s) affected)
B. Use > and < instead of BETWEEN

This example, which uses greater than (>) and less than (<) operators, returns different results because these operators are not inclusive.

USE pubs
GO
SELECT title_id, ytd_sales      
FROM titles      
WHERE ytd_sales > 4095 AND ytd_sales < 12000      
GO

Here is the result set:

title_id ytd_sales   
-------- ----------- 
PC1035   8780        

(1 row(s) affected)
C. Use NOT BETWEEN

This example finds all rows outside a specified range (from 4,095 through 12,000).

USE pubs
GO
SELECT title_id, ytd_sales
FROM titles
WHERE ytd_sales NOT BETWEEN 4095 AND 12000
GO

Here is the result set:

title_id ytd_sales   
-------- ----------- 
BU1111   3876        
BU2075   18722       
MC2222   2032        
MC3021   22246       
PS1372   375         
PS2091   2045        
PS2106   111         
PS3333   4072        
PS7777   3336        
TC3218   375         
TC4203   15096       

(11 row(s) affected)

See Also

> (Greater Than)

< (Less Than)

Expressions

Functions

Operators (Logical Operators)

SELECT (Subqueries)

WHERE

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.