Curriculum
in this tutorial, you will learn how to use the SQL Server BETWEEN
 operator to specify a range to test.
BETWEEN
 operatorThe BETWEEN
 operator is a logical operator that allows you to specify a range to test.
The following illustrates the syntax of the BETWEEN
 operator:
column | expression BETWEEN start_expression AND end_expression
In this syntax:
start_expression
 and end_expression
 between the BETWEEN
 and the AND
 keywords. The start_expression
, end_expression
 and the expression
 to test must have the same data type.The BETWEEN
 operator returns TRUE
 if the expression to test is greater than or equal to the value of the start_expression
 and less than or equal to the value of the end_expression
.
You can use the greater than or equal to (>=) and less than or equal to (<=) to substitute the BETWEEN
 operator as follows:
column | expression <= end_expression AND column | expression >= start_expression
The condition that uses the BETWEEN
 operator is much more readable the one that uses the comparison operators >=, <= and the logical operator AND
.
To negate the result of the BETWEEN
 operator, you use NOT BETWEEN
 operator as follows:
column | expression NOT BETWEEN start_expression AND end_expresion
The NOT BETWEEN
 returns TRUE
 if the value in the column or expression is less than the value of the  start_expression
 and greater than the value of the end_expression
. It is equivalent to the following condition:
column | expression < start_expression AND column | expression > end_expression
BETWEEN
 examplesLet’s take some examples of using the BETWEEN
 operator to understand how it works.
BETWEEN
 with numbers exampleThe following query finds the products whose list prices are between 149.99 and 199.99:
SELECT product_id, product_name, list_price FROM production.products WHERE list_price BETWEEN 149.99 AND 199.99 ORDER BY list_price;
To get the products whose list prices are not in the range of 149.99 and 199.99, you use the NOT BETWEEN
 operator as follows:
SELECT product_id, product_name, list_price FROM production.products WHERE list_price NOT BETWEEN 149.99 AND 199.99 ORDER BY list_price;
BETWEEN
 with dates exampleThe following query finds the orders that customers placed between January 15, 2017
 and January 17, 2017
:
SELECT order_id, customer_id, order_date, order_status FROM sales.orders WHERE order_date BETWEEN '20170115' AND '20170117' ORDER BY order_date;
Notice that to specify a literal date, you use the format ‘YYYYMMDD
‘ where YYYY
 is 4-digits year e.g., 2017, MM
 is 2-digits month e.g., 01 and DD
 is 2-digits day e.g., 15.