Does MS SQL Server's "between" include the range boundaries?
Indeed, the BETWEEN operator in SQL Server is inclusive of the range boundaries.
Take as an example: column BETWEEN 1 AND 3 will select values 1, 2, and 3.
The Catch with DateTimes
When using DateTime or DateTime2 data types, treading carefully around boundaries is a must. Let's say myDate BETWEEN '2023-04-01' AND '2023-04-30' will include every moment from the break of April 1st to the twilight of April 30th. To eliminate the risk of missing out the very last second of the day records, refrain from setting the upper boundary as 'lastday 23:59:59'.
Precision Play with DATEADD
In scenarios demanding more precision, like recording events at millisecond-level granularity, embrace the DATEADD function. Adjust the upper boundary as DATEADD(millisecond, -3, 'end_date') to have the end date 3 milliseconds before midnight of the subsequent day, hence assuring inclusivity.
Simplifying the Queries: The Date-Only Scenario
If the time attribute isn't strictly required, you can eliminate it thereby making your query simpler. Adopting myDate >= 'start_date' AND myDate < 'end_date' + 1 not only covers all dates within the range but also prevents timing complexities.
Foolproof Tips for Effective Queries
Avoid Raindance Around Functions in WHERE Clause
Boosting performance involves avoiding the use of functions in the WHERE clause which can stifle query execution by rendering your indexes useless.
Ensuring Data Type Consistency
Ensure that your BETWEEN operator expressions have the same data type. Non-alignment can spring unexpected results or deliver performance lag due to implicit data-type conversions.
Testing is No Jest
It's of paramount importance to authenticate your queries with basic data to assure that the expected records are obtained, especially when dealing with DateTime boundaries.
The Artful Usage of NOT BETWEEN
If you need your selectiveness to run in the opposite direction, NOT BETWEEN can be your trusty tool, just bear in mind, it's exclusive of the boundaries.
Was this article helpful?