What is the use of the BETWEEN operator in SQL?

The BETWEEN operator in SQL is used to filter data within a specific range — it checks whether a value lies between two given boundaries, inclusive of both the lower and upper limits. It’s especially useful for filtering numeric ranges, dates, or even text values in alphabetical order.

For instance, if I want to retrieve all employees whose salaries fall between 40,000 and 60,000, I’d write:

SELECT * 
FROM Employees 
WHERE Salary BETWEEN 40000 AND 60000;

This query will include employees with salaries exactly equal to 40,000 or 60,000 as well — because BETWEEN is inclusive by default.

Similarly, I’ve used BETWEEN extensively with date ranges. For example, when generating monthly reports, I might query:

SELECT * 
FROM Orders 
WHERE OrderDate BETWEEN '2025-10-01' AND '2025-10-31';

This helps easily extract records for a specific period without having to write multiple conditions like
OrderDate >= '2025-10-01' AND OrderDate <= '2025-10-31'.

In one of my projects, I applied this in a financial dashboard where users could filter transactions within a date or amount range. It made the filtering logic more readable and user-friendly on the backend.

A challenge I’ve faced with BETWEEN is when working with date and time data types — for example, if the upper limit has a timestamp (like '2025-10-31 23:59:59'), missing that precision could exclude some rows. To handle that, I usually either cast to date only or use <= with the next day boundary.

Another limitation is that BETWEEN assumes ascending order — so BETWEEN 60 AND 40 would return no results, even though the range logically exists. Hence, maintaining the correct order of values is important.

Overall, BETWEEN is a clean, readable way to filter within ranges — ideal for numeric, date, or even text-based comparisons — though careful handling of date boundaries ensures accurate results.