Between operator in sql Server

The BETWEEN operator is a logical operator that allows you to specify a range to test.
The following illustrates the syntax of the BETWEEN operator:
In this syntax:
  • First, specify the column or expression to test.
  • Second, place the  start_expression and end_expression between the BETWEEN and the AND keywords. The start_expressionend_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:
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:
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:
Note that if any input to the BETWEEN or NOT BETWEEN is NULL, then the result is UNKNOWN.

SQL Server BETWEEN examples

Let’s take some examples of using the BETWEEN operator to understand how it works.

A) Using SQL Server BETWEEN with numbers example

See the following products table from the sample database:
The following query finds the products whose list prices are between 149.99 and 199.99:
SQL Server BETWEEN Example
To get the products whose list prices are in the range 149.99 and 199.99, you use the NOT BETWEEN operator as follows:
SQL Server NOT BETWEEN example

B) Using SQL Server BETWEEN with dates example

Consider the following orders table:
The following query finds the orders that customers placed between January 15, 2017 and January 17, 2017:
SQL Server BETWEEN dates example
Notice that to specify a date constant, 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.
In this tutorial, you have learned how to use the SQL Server BETWEEN operator to form a condition that tests against a range of values

Post a Comment