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

User · Answer

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

User · Answer

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

User · Answer

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  gt    20090101 00 00 00  AND myDate  lt   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  gt    firstday  AND myDate  lt   Lastday 1  approach instead   Good article on this issue here

User · Answer

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  gt    20090101 00 00 00  AND myDate  lt  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  gt    20090101  AND myDate  lt  DATEADD day 1  20090101     and not worry about it

User · Answer

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   lt    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

User · Answer

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  gt    20160601  AND myDateTime  lt   20160701

User · Answer

I ve always used this   WHERE myDate BETWEEN startDate AND  endDate 1

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to between