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

Question

For instance can

SELECT foo
FROM bar
WHERE foo BETWEEN 5 AND 10

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

Long story short; it's inclusive, at both ends. If you want inclusive at one end and exclusive at the other, or both exclusive, use two predicates and `<` `>` `>=` `<=` appropriately — Caius Jard, May 07 '22 at 09:16

score 283 · Accepted Answer · edited Jul 20 '18 at 14:37

283

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'

edited Jul 20 '18 at 14:37

Ian Boyd

246,734
253
869
1,219

answered Apr 14 '09 at 22:36

DJ.

16,045
3
42
46

15

When using BETWEEN to filter DateTimes between two dates, you can also cast the DateTime to a Date, eg: where CONVERT(DATE, MyDate) BETWEEN '2017-09-01' and '2017-09-30' This approach makes the time element of the DateTime irrelevant – Pete Sep 15 '17 at 09:30
3

Be sure not to try to subtract 3 ms from a date; you'll miss items from those 3 ms. And you also don't want to `CONVERT` a **datetime** to a **date**, as that will render indexes useless. Use the standard `WHERE OrderDate >= '20160601' AND OrderDate < '20160701'`. Also, be sure to use `yyyymmdd`, as `yyyy-mm-dd` is locale dependent, and will be misinterpreted depending on your server's `mdy, dmy, ymd, ydm, myd, and dym` setting. – Ian Boyd Jul 20 '18 at 14:32
@IanBoyd I thought yyyy-mm-dd was universal, and that no culture uses yyyy-dd-mm as a date format. I think one uses yyyy.dd.mm, but even if you set your culture to that, it will still interpret yyyy-mm-dd correctly. Do you have an example culture that I could test with? – Robert McKee Aug 24 '22 at 17:02
`dz-BT` uses `yyyy-mm-dd`. As does `en-CA`, `fr-CA`, `xh-ZA`, `rw-RW`, `ko-KR`, `lt-LT`, `smj-SE`, `se-NO`, `se-SE`, `sma-SE`, `st-ZA`, `nso-ZA`, `tn-BW`, `tn-ZA`, `si-LK`, `sv-SE`, `ug-CN`, `ve-ZA`, `ts-ZA`. – Ian Boyd Aug 24 '22 at 17:39

score 268 · Answer 2 · edited Jul 20 '18 at 14:39

268

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.

edited Jul 20 '18 at 14:39

Ian Boyd

246,734
253
869
1,219

answered Apr 14 '09 at 23:00

BradC

39,306
13
73
89

1

Similar issues with strings as well `WHERE col BETWEEN 'a' AND 'z'` will exclude most of the z rows for example. – Martin Smith Apr 22 '11 at 10:29
8

This point is right of course; but ought not to be surprising if you're working with datetimes. It's analogous to pointing out that `BETWEEN 5 AND 10` doesn't include `10.2`... – Andrzej Doyle Aug 28 '12 at 09:53
4

`CAST`ing the `datetime` as a `DATE` would work: `CAST(DATE_TIME_COL AS DATE) BETWEEN '01/01/2009' AND '01/31/2009'`. – craig Nov 17 '14 at 18:09
2

@craig, that's true, as long as you are using SQL 2008 or higher, which is when the Date datatype was introduced. Also, that syntax will convert that value for every single row, so won't be able to use any indexes on that field (if that is a concern). – BradC Nov 25 '14 at 15:01
`It is entirely possible to have records created within that last second of the day, with a datetime as late as 01/01/2009 23:59:59.997` <-- couldnt you just then use `AND '01/31/2009 23:59:59.99999999'` or however many 9's are required – wal Jul 27 '16 at 04:39
*so will miss anything that occurred during the day of Jan 31st*. - not true. Events occurring at exactly midnight on jan 31st are included – Caius Jard May 07 '22 at 09:19
@CaiusJard That's what I meant by "during the day of"; anything later than 0:00:00 on the time component will not be included. – BradC May 09 '22 at 13:27
00:00:00 is "during the day of" – Caius Jard May 09 '22 at 15:06

score 17 · Answer 3 · edited Sep 07 '18 at 04:41

17

Real world example from SQL Server 2008.

Source data:

ID    Start
1     2010-04-30 00:00:01.000
2     2010-04-02 00:00:00.000
3     2010-05-01 00:00:00.000
4     2010-07-31 00:00:00.000

Query:

SELECT
    *
FROM
    tbl
WHERE
    Start BETWEEN '2010-04-01 00:00:00' AND '2010-05-01 00:00:00'

Results:

ID    Start
1     2010-04-30 00:00:01.000
2     2010-04-02 00:00:00.000

alt text

edited Sep 07 '18 at 04:41

Dai

141,631
28
261
374

answered Jul 30 '10 at 19:45

Ryan Rodemoyer

5,548
12
44
54

I didn't get your answer, to be honest. Maybe my internet provider has hidden your screenshot if you posted one. – anar khalilov Jan 14 '14 at 16:03
3

Why is the row with `ID = 3` excluded? Its `Start` value is equal to the `BETWEEN` upper-bound value, and `BETWEEN` is an inclusive range, not an exclusive upper-bounded range. – Dai Sep 07 '18 at 04:42
Better answer with results. – Samir Jan 07 '19 at 08:20

score 13 · Answer 4 · edited Jul 20 '18 at 14:41

13

if you hit this, and don't really want to try and handle adding a day in code, then let the DB do it..

myDate >= '20090101 00:00:00' AND myDate < DATEADD(day,1,'20090101 00:00:00')

If you do include the time portion: make sure it references midnight. Otherwise you can simply omit the time:

myDate >= '20090101' AND myDate < DATEADD(day,1,'20090101')

and not worry about it.

edited Jul 20 '18 at 14:41

Ian Boyd

246,734
253
869
1,219

answered Dec 26 '11 at 18:29

Shaun

131
1
2

score 12 · Answer 5 · edited Dec 26 '17 at 05:22

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.

score 4 · Answer 6 · answered Jan 30 '14 at 02:57

4

If the column data type is datetime then you can do this following to eliminate time from datetime and compare between date range only.

where cast(getdate() as date) between cast(loginTime as date) and cast(logoutTime as date)

answered Jan 30 '14 at 02:57

Kahlil Vanz

169
1
3

This works better than adding +1 to the end date. I agree with Andrew Morton - if it is not sargable it may improve performance to change column data type or add a second column with pre-computed dates only. – Arno Peters Aug 16 '17 at 06:52

score 0 · Answer 7 · answered Feb 27 '17 at 13:44

It does includes boundaries.

declare @startDate date = cast('15-NOV-2016' as date) 
declare @endDate date = cast('30-NOV-2016' as date)
create table #test (c1 date)
insert into #test values(cast('15-NOV-2016' as date))
insert into #test values(cast('20-NOV-2016' as date))
insert into #test values(cast('30-NOV-2016' as date))
select * from #test where c1 between @startDate and @endDate
drop table #test
RESULT    c1
2016-11-15
2016-11-20
2016-11-30


declare @r1 int  = 10
declare @r2 int  = 15
create table #test1 (c1 int)
insert into #test1 values(10)
insert into #test1 values(15)
insert into #test1 values(11)
select * from #test1 where c1 between @r1 and @r2
drop table #test1
RESULT c1
10
11
15

score -3 · Answer 8 · answered Mar 24 '15 at 01:32

-3

I've always used this:

WHERE myDate BETWEEN startDate AND (endDate+1)

answered Mar 24 '15 at 01:32

Bakchod Guru

51
1
6

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

8 Answers8

datetime2 and datetimeoffset

Linked