Is MS SQL Server's `BETWEEN` Operator Inclusive or Exclusive?

Inclusive Range Retrieval with MS SQL Server's "BETWEEN" Operator

When using MS SQL Server's "BETWEEN" operator, it's essential to understand whether the range boundaries are included or excluded in the selection criteria.

To answer the specific question, the "BETWEEN" operator is inclusive. This means that, by default, the values specified as the lower and upper bounds are included in the result set. In the example provided:

SELECT foo
FROM bar
WHERE foo BETWEEN 5 AND 10

The query will select all rows where the value of the "foo" column is between (and including) 5 and 10.

However, this inclusive behavior can present a caveat when working with DateTimes. By default, when only a date is specified, SQL Server treats it as midnight on that day. This means that if you want to capture all values within a specific day, you must subtract 3 milliseconds from the end date to avoid missing data falling within the 3 milliseconds before midnight.

For example, to retrieve all values within June 2016, the correct query would be:

where myDateTime between '20160601' and DATEADD(millisecond, -3, '20160701')

This ensures that you capture all values within June 2016, including those falling within the 3 milliseconds before midnight on June 30th.

It's important to note that, while for most data types the "BETWEEN" operator is inclusive, there are exceptions. For instance, with "datetime2" and "datetimeoffset" data types, it's generally recommended to use the ">=" and "<" operators instead of "BETWEEN" to avoid potential range truncation issues.

The above is the detailed content of Is MS SQL Server's `BETWEEN` Operator Inclusive or Exclusive?. For more information, please follow other related articles on the PHP Chinese website!