Does SQL Server's `BETWEEN` Operator Include the Start and End Values?

Does MS SQL Server's "Between" Operator Include Range Boundaries?

The BETWEEN operator in MS SQL Server, as its name suggests, checks whether a given expression falls within a specified range. However, a common question arises: does this range include or exclude the boundary values?

The answer is that BETWEEN is an inclusive operator, meaning it includes both the lower and upper bounds of the range. To clarify:

SELECT foo
FROM bar
WHERE foo BETWEEN 5 AND 10

This query will retrieve rows where foo is >= 5 and <= 10, effectively including the values 5 and 10 in the result. This is confirmed by Microsoft's documentation:

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 Caveats

For DateTime values, there is a subtle consideration to keep in mind. If only a date is specified, it is interpreted as midnight on that day. To avoid missing or duplicating time values, it is recommended to use a range that excludes 3 milliseconds before midnight on the end date.

For example, to retrieve all DateTime values within June 2016:

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

This ensures that the range extends to the end of the month without including any values from July 1st.

datetime2 and datetimeoffset

For these data types, it is safer to use the >= and < operators instead of BETWEEN. For example:

WHERE myDateTime >= '20160601' AND myDateTime < '20160701'
The above is the detailed content of Does SQL Server's `BETWEEN` Operator Include the Start and End Values?. For more information, please follow other related articles on the PHP Chinese website!