Keywords: SQL Server | BETWEEN operator | DateTime handling
Abstract: This article provides an in-depth examination of the BETWEEN operator in MS SQL Server, focusing on its inclusive boundary behavior. Through examples involving numeric and DateTime data types, it elucidates the operator's mechanism of including both start and end values. Special attention is given to potential pitfalls with DateTime types, such as precision-related boundary omissions, and optimized solutions using >= and < combinations are recommended to ensure query accuracy and completeness.
Boundary Inclusivity of the BETWEEN Operator
In MS SQL Server, the BETWEEN operator is designed to select values within a specified range, and it is inclusive of the boundary values. According to official documentation, BETWEEN returns TRUE when the condition test_expression >= begin_expression AND test_expression <= end_expression is met. This means that both the begin_expression and end_expression are included in the query results.
Application of BETWEEN with Numeric Types
For numeric types, the behavior of BETWEEN is straightforward and easy to understand. For instance, the query SELECT foo FROM bar WHERE foo BETWEEN 5 AND 10 will return all records where foo is greater than or equal to 5 and less than or equal to 10, including the values 5 and 10. This inclusivity makes BETWEEN convenient for handling continuous numeric ranges.
Challenges with DateTime Types in BETWEEN
However, when using BETWEEN with DateTime types, special caution is required due to time precision issues. In SQL Server, the datetime type has a precision of 3.33 milliseconds. If only the date part is specified, the system defaults to midnight (00:00:00.000) of that date. For example, BETWEEN '20160601' AND '20160701' is effectively equivalent to BETWEEN '20160601 00:00:00.000' AND '20160701 00:00:00.000', which would miss all data from after midnight on June 30, 2016, to before midnight on July 1, 2016.
Solutions for DateTime Boundary Issues
To address this problem, a common but not recommended approach is to adjust the end time, such as using BETWEEN '20160601' AND DATEADD(millisecond, -3, '20160701') or BETWEEN '20160601 00:00:00.000' AND '20160630 23:59:59.997'. However, this method carries risks because subtracting 3 milliseconds might cause the query to miss records within that time window.
Recommended Use of >= and < Combinations
A more reliable method is to avoid BETWEEN altogether and use a combination of >= and <. For example, to query data for June 2016, write WHERE myDateTime >= '20160601' AND myDateTime < '20160701'. This approach explicitly includes all times from midnight on June 1 onwards and excludes times from midnight on July 1 onwards, ensuring complete coverage of June data without precision-related omissions.
BETWEEN with Other Data Types
BETWEEN is also applicable to text and numeric ranges. For instance, querying products with prices between 10 and 20 in a products table: SELECT * FROM Products WHERE Price BETWEEN 10 AND 20. For text, such as selecting product names between 'Carnarvon Tigers' and 'Mozzarella di Giovanni': SELECT * FROM Products WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni' ORDER BY ProductName. These applications adhere to the principle of boundary inclusivity.
Summary and Best Practices
The BETWEEN operator in MS SQL Server always includes boundary values, but when dealing with DateTime types, be wary of query incompleteness due to default time settings. Prioritizing the use of >= and < combinations can effectively avoid such issues, enhancing query accuracy. In practical development, choose the appropriate method based on the data type and business requirements to ensure precise and efficient data retrieval.