SQL "between" not inclusive

Understanding SQL BETWEEN: A Deep Dive into Inclusivity

Explore the behavior of the SQL BETWEEN operator, its common misconceptions regarding inclusivity, especially with dates, and learn best practices for precise range queries.

The SQL BETWEEN operator is a convenient way to specify a range of values in a WHERE clause. It simplifies queries that would otherwise require multiple AND conditions. However, a common misconception, particularly among new SQL users, is how BETWEEN handles the start and end values of the range. Many assume it might be exclusive of one or both ends, similar to how ranges are often defined in programming languages like Python or Java. This article will clarify that BETWEEN is, in fact, inclusive of both its start and end values, and will provide practical examples, especially focusing on date and time data types where this behavior can lead to unexpected results.

The Inclusive Nature of SQL BETWEEN

Contrary to some programming language conventions, the SQL BETWEEN operator is inherently inclusive. This means that when you specify value BETWEEN start_value AND end_value, the query will return rows where value is greater than or equal to start_value AND value is less than or equal to end_value. This applies to numeric, character, and datetime data types alike. Understanding this fundamental aspect is crucial for writing accurate and predictable SQL queries.

SELECT ProductID, ProductName, ListPrice
FROM Production.Product
WHERE ListPrice BETWEEN 10.00 AND 20.00;

This query retrieves products with a list price from 10.00 up to and including 20.00.

SELECT ProductID, ProductName, ListPrice
FROM Production.Product
WHERE ListPrice >= 10.00 AND ListPrice <= 20.00;

This demonstrates the explicit equivalent of the BETWEEN operator, confirming its inclusive nature.

Visualizing the inclusive range of BETWEEN.

Challenges with Date and Time Data Types

While the inclusive nature of BETWEEN is straightforward for simple numeric or character ranges, it can become a source of subtle bugs when dealing with DATE, DATETIME, DATETIME2, or SMALLDATETIME data types. The issue arises because date columns often implicitly include a time component, even if it's '00:00:00'. When you specify a date like '2023-01-01', it typically translates to '2023-01-01 00:00:00.000'. If your end_date in a BETWEEN clause is '2023-01-31', it means '2023-01-31 00:00:00.000', effectively excluding any records from January 31st that have a time component after midnight.

SELECT OrderID, OrderDate, TotalDue
FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '2023-01-01' AND '2023-01-31';

This query might exclude orders placed on January 31, 2023, if they have a time component greater than 00:00:00.

Best Practices for Date Range Queries

To avoid the pitfalls of BETWEEN with datetime columns, especially when you want to include all records for a given end date, it's generally safer and more explicit to use direct comparison operators. The most common and recommended approach is to use >= for the start date and < for the day immediately after your desired end date. This ensures that all times within the desired end date are included, up to, but not including, the first moment of the next day.

SELECT OrderID, OrderDate, TotalDue
FROM Sales.SalesOrderHeader
WHERE OrderDate >= '2023-01-01' AND OrderDate < '2023-02-01';

This query correctly includes all orders from January 1, 2023, through the end of January 31, 2023, regardless of their time component.

Alternatively, if you strictly want to use BETWEEN and ensure inclusivity for the entire end date, you must explicitly specify the end of the day. This often involves converting the end date to its latest possible time, which can vary depending on the precision of your datetime data type (e.g., 23:59:59.997 for DATETIME or 23:59:59.9999999 for DATETIME2).

SELECT OrderID, OrderDate, TotalDue
FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '2023-01-01' AND '2023-01-31 23:59:59.997';

This approach includes records up to the last millisecond of January 31st for DATETIME columns.

In conclusion, while SQL's BETWEEN operator is undeniably inclusive, its interaction with datetime data types requires careful consideration. For robust and unambiguous date range queries, especially when dealing with time components, the column >= start_date AND column < next_day pattern is often the safest and most recommended approach. Always test your date range queries thoroughly to ensure they return the expected results.