The TSQL Between operator function is used to return results based on comparing between two expression values. Those expression values are in numbers or date format.

Let says we have the following product table.

We are going to use this table to exercise the Between operator so we can narrow the result by comparing the start and end expression values. 

1. We want to get all the products that have a price value between $50 and $200 dollars. To get those results we are going to comparing the price value field.

SELECT *
FROM PRODUCTS
WHERE Price BETWEEN 50 AND 200

2. If you want to get all products with a range price of $50 and $200. You can easily include a NOT operator before the BETWEEN operator. See the below example.

SELECT *
FROM PRODUCTS
WHERE Price NOT BETWEEN 50 AND 200

3. The BETWEEN operator can also compare between two dates. Let says we want to get all products that are listed between 1 Dec 2020 to 31 Dec 2020.

SELECT *
FROM PRODUCTS
WHERE PromotionDate BETWEEN '2020-12-31' AND '2020-12-01'

Please remember the start expression always using the lower value compared to the end expression.

See the following example. If you try to run the following query. It will not return any result.

SELECT *
FROM PRODUCTS
WHERE Price BETWEEN 200 AND 50