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

Inclusive Nature of MS SQL Server's "BETWEEN" Operator

Does the MS SQL Server's BETWEEN operator include the range boundaries? In other words, does the following query:

SELECT foo
FROM bar
WHERE foo BETWEEN 5 AND 10

select values that are exactly 5 and 10, or are these values excluded from the range?

Answer:

The BETWEEN operator in MS SQL Server is inclusive, which means that it includes both the starting and ending values in the specified range. Therefore, the query above will select rows where the foo column has values between and including 5 and 10.

From Books Online:

BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.

DateTime Caveat:

When working with DateTimes, it's important to note that if only a date is provided, the value is treated as midnight on that day. To avoid missing times within the end date or duplicating data capture for the following day's data, it's recommended to subtract 3 milliseconds from the end date. This is because any value less than 3 milliseconds will be rounded up to midnight the next day.

Example:

To get all values within June 2016, use the following query:

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

This translates to:

where myDateTime between '20160601 00:00:00.000' and '20160630 23:59:59.997'

datetime2 and datetimeoffset:

Subtracting 3 ms from a date can lead to missing rows in the 3 ms window. The most effective solution is to use the following operators:

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

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