The SQL Server BETWEEN operator allows you to test between a range of values in your queries. For example, you could test that a value is between two numbers.

The BETWEEN operator is inclusive, meaning that it includes the values that you specify within the range. That is, it includes values that are greater than or equal to the lower value, and values that are less than or equal to the higher value. Therefore it’s like using >= and <=.

Example

Here’s an example to demonstrate.

SELECT Name, Population
FROM city
WHERE Population BETWEEN 22000 AND 24000
ORDER BY Population ASC;

Result:

+--------------+--------------+
| Name         | Population   |
|--------------+--------------|
| Thimphu      | 22000        |
| Weno         | 22000        |
| Nuku´alofa   | 22400        |
| Douglas      | 23487        |
| Saint John´s | 24000        |
+--------------+--------------+

As mentioned, BETWEEN is inclusive, so the result includes the two values that I specified to the left and right of the AND argument.

This is the equivalent of doing the following.

SELECT Name, Population
FROM city
WHERE Population >= 22000 AND Population <= 24000
ORDER BY Population ASC;

Result:

+--------------+--------------+
| Name         | Population   |
|--------------+--------------|
| Thimphu      | 22000        |
| Weno         | 22000        |
| Nuku´alofa   | 22400        |
| Douglas      | 23487        |
| Saint John´s | 24000        |
+--------------+--------------+

NOT BETWEEN

You can use the NOT argument to specify that the value should not be between the two values specified.

SELECT Name, Population
FROM city
WHERE Population NOT BETWEEN 300 AND 9696300
ORDER BY Population ASC;

Result:

+-----------------+--------------+
| Name            | Population   |
|-----------------+--------------|
| Adamstown       | 42           |
| West Island     | 167          |
| São Paulo       | 9968485      |
| Seoul           | 9981619      |
| Mumbai (Bombay) | 10500000     |
+-----------------+--------------+

This time we get the same result that we would if we’d used the less than and greater than operators.

SELECT Name, Population
FROM city
WHERE Population < 300 OR Population > 9696300
ORDER BY Population ASC;

Result:

+-----------------+--------------+
| Name            | Population   |
|-----------------+--------------|
| Adamstown       | 42           |
| West Island     | 167          |
| São Paulo       | 9968485      |
| Seoul           | 9981619      |
| Mumbai (Bombay) | 10500000     |
+-----------------+--------------+

When using NOT BETWEEN, if we wanted to include the specified values in the test, we would need to increment/decrement the values.

SELECT Name, Population
FROM city
WHERE Population NOT BETWEEN 301 AND 9696299
ORDER BY Population ASC;

Result:

+-----------------+--------------+
| Name            | Population   |
|-----------------+--------------|
| Adamstown       | 42           |
| West Island     | 167          |
| Fakaofo         | 300          |
| Shanghai        | 9696300      |
| São Paulo       | 9968485      |
| Seoul           | 9981619      |
| Mumbai (Bombay) | 10500000     |
+-----------------+--------------+

This time we see that two cities are added to our result set; Fakaofo with a population of 300, and Shanghai with a population of 9696300.

By the way, this is all old sample data so the population figures probably don’t reflect actual reality in many cases.

Here’s how we’d do it with the Less Than or Equal To and Greater Than or Equal To operators.

SELECT Name, Population
FROM city
WHERE Population <= 300 OR Population >= 9696300
ORDER BY Population ASC;

Result:

+-----------------+--------------+
| Name            | Population   |
|-----------------+--------------|
| Adamstown       | 42           |
| West Island     | 167          |
| Fakaofo         | 300          |
| Shanghai        | 9696300      |
| São Paulo       | 9968485      |
| Seoul           | 9981619      |
| Mumbai (Bombay) | 10500000     |
+-----------------+--------------+

BETWEEN with Dates

When you use the BETWEEN operator on dates (such as datetime, datetime2, or datetimeoffset values), it’s best to use the format YYYYMMDD in order to avoid any locale issues (for example, if the user has changed the date format for their session).

Also, if you don’t provide the time component,  it defaults to 12:00 A.M.

Therefore, the following query wouldn’t return any rows that contains a time part after 12:00 A.M. on 1973-12-31.

SELECT * FROM Artists
WHERE ActiveFrom  BETWEEN '20010101' AND '20011231';

You can either adjust your date values accordingly, or you might prefer to use the Greater Than or Equal To and Less Than operators.

Like this:

SELECT * FROM Artists
WHERE ActiveFrom  >= '20010101' AND ActiveFrom < '20011231';