Does MS SQL Server's "between" include the range boundaries?
SqlSql ServerBetweenSql Problem Overview
For instance can
SELECT foo
FROM bar
WHERE foo BETWEEN 5 AND 10
select 5 and 10 or they are excluded from the range?
Sql Solutions
Solution 1 - Sql
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'
Solution 2 - Sql
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.
Solution 3 - Sql
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
Solution 4 - Sql
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.
Solution 5 - Sql
> 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.
Solution 6 - Sql
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)
Solution 7 - Sql
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
Solution 8 - Sql
I've always used this:
WHERE myDate BETWEEN startDate AND (endDate+1)