Does MS SQL Server's "between" include the range boundaries?

user_90826 0
Viewed 288825

For instance can 

SELECT foo
FROM bar
WHERE foo BETWEEN 5 AND 10

select 5 and 10 or they are excluded from the range?

between sql-server sql
8 Answers

The BETWEEN operator is inclusive.

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

NB: With DateTimes you have to be careful; if only a date is given the value is taken as of midnight on that day; to avoid missing times within your end date, or repeating the capture of the following day's data at midnight in multiple ranges, your end date should be 3 milliseconds before midnight on of day following your to date. 3 milliseconds because any less than this and the value will be rounded up to midnight the next day.

e.g. to get all values within June 2016 you'd need to run:

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

i.e.

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

datetime2 and datetimeoffset

Subtracting 3 ms from a date will leave you vulnerable to missing rows from the 3 ms window. The correct solution is also the simplest one:

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

Yes, but be careful when using between for dates.

BETWEEN '20090101' AND '20090131'

is really interpreted as 12am, or

BETWEEN '20090101 00:00:00' AND '20090131 00:00:00'

so will miss anything that occurred during the day of Jan 31st. In this case, you will have to use:

myDate >= '20090101 00:00:00' AND myDate < '20090201 00:00:00'  --CORRECT!

or

BETWEEN '20090101 00:00:00' AND '20090131 23:59:59' --WRONG! (see update!)

UPDATE: It is entirely possible to have records created within that last second of the day, with a datetime as late as 20090101 23:59:59.997!!

For this reason, the BETWEEN (firstday) AND (lastday 23:59:59) approach is not recommended.

Use the myDate >= (firstday) AND myDate < (Lastday+1) approach instead.

Good article on this issue here.

BradC_213980

BETWEEN (Transact-SQL)

Specifies a(n) (inclusive) range to test.

test_expression [ NOT ] BETWEEN begin_expression AND end_expression

Arguments

test_expression

Is the expression to test for in the range defined by begin_expression and end_expression. test_expression must be the same data type as both begin_expression and end_expression.

NOT

Specifies that the result of the predicate be negated.

begin_expression

Is any valid expression. begin_expression must be the same data type as both test_expression and end_expression.

end_expression

Is any valid expression. end_expression must be the same data type as both test_expression and begin_expression.

AND

Acts as a placeholder that indicates test_expression should be within the range indicated by begin_expression and end_expression.

Remarks

To specify an exclusive range, use the greater than (>) and less than operators (<). If any input to the BETWEEN or NOT BETWEEN predicate is NULL, the result is UNKNOWN.

Result Value

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.

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

Russ_Cam_18310
Related