Does MS SQL Server's `BETWEEN` Operator Include Boundary Values?

Do Boundary Values Apply to MS SQL Server's "between" Operator?

The "between" operator in MS SQL Server is inclusive, as defined in Books Online. This means that values greater than or equal to the lower bound ("begin_expression") and less than or equal to the upper bound ("end_expression") will be included in the result set.

For example, the following query will select all rows where the "foo" column contains values between 5 and 10, inclusive:

SELECT foo
FROM bar
WHERE foo BETWEEN 5 AND 10

DateTime Considerations

When working with DateTime values, it's important to note that only the date portion is considered in the "between" comparison. To ensure accurate results, you must specify a time component that is 3 milliseconds before midnight for the end date. This prevents any data loss or duplication for values that fall within the end date range.

For instance, to retrieve all rows where the "myDateTime" column contains values within June 2016, you would use the following query:

WHERE myDateTime BETWEEN '20160601' AND DATEADD(millisecond, -3, '20160701')

Datetime2 and Datetimeoffset Operations

Instead of subtracting 3 milliseconds, it is recommended to use the following simpler and more accurate approach when working with datetime2 or datetimeoffset data types:

WHERE myDateTime >= '20160601' AND myDateTime < '20160701'

The above is the detailed content of Does MS SQL Server's `BETWEEN` Operator Include Boundary Values?. For more information, please follow other related articles on the PHP Chinese website!