http://www.sqlservercentral.com/blogs/sqltact/2012/09/26/how-between-is-inclusive/

Printed 2014/10/02 01:02AM

How BETWEEN is inclusive

By William Assaf, 2012/09/26

This is a old and simple one, but an important subtlety that TSQL developers must be aware of.

Using the BETWEEN syntax is easy enough in a WHERE clause, and it's pretty obvious for numbers.

use testing
go

create table decbetweentesting
(      id int identity(1,1) not null primary key
,      testdec decimal(19,5) not null
)
go
insert into decbetweentesting (testdec) values (.99), (1), (1.01), (2), (2.01)
go
select * from decbetweentesting where testdec between '1' and '2'

And the long form equivalent

select * from decbetweentesting where testdec >= '1' and testdec <= '2'


id testdec
2 1.00000
3 1.01000
4 2.00000


id testdec
2 1.00000
3 1.01000
4 2.00000


Easy, right?

So don't mistake that simple logic for dates.


create table datebetweentesting
(      id int identity(1,1) not null primary key
,      testdate datetime not null
)
go
insert into datebetweentesting (testdate) values ('12/29/2011 23:59:59'), ('12/30/2011 00:00:00'), ('12/30/2011 00:01:00'), ('12/31/2011 00:00:00'), ('12/31/2011 00:01:00'), ('1/1/2012 00:00:00'), ('1/1/2012 00:01:00')
go
select * from datebetweentesting where testdate between '12/30/2011' and '12/31/2011'
select * from datebetweentesting where testdate >= '12/30/2011' and testdate <= '12/31/2011'


id testdate
2 2011-12-30 00:00:00.000
3 2011-12-30 00:01:00.000
4 2011-12-31 00:00:00.000


id testdate
2 2011-12-30 00:00:00.000
3 2011-12-30 00:01:00.000
4 2011-12-31 00:00:00.000



This simple query to get everything before the end of the year will IGNORE things that happened during the date of 12/31/2011, after midnight.  Almost certainly not what you want.

But, don't fall for this:


select * from datebetweentesting where testdate between '12/30/2011' and '1/1/2012'
select * from datebetweentesting where testdate >= '12/30/2011' and testdate <= '1/1/2012'

 id testdate
2 2011-12-30 00:00:00.000
3 2011-12-30 00:01:00.000
4 2011-12-31 00:00:00.000
5 2011-12-31 00:01:00.000
6 2012-01-01 00:00:00.000

id testdate
2 2011-12-30 00:00:00.000
3 2011-12-30 00:01:00.000
4 2011-12-31 00:00:00.000
5 2011-12-31 00:01:00.000
6 2012-01-01 00:00:00.000


Which would return data from the new year.

In other words, to catch the end of 2011 and nothing in 2012, don't use BETWEEN.


select * from datebetweentesting where testdate >= '12/30/2011' and testdate < '1/1/2012'



id testdate
2 2011-12-30 00:00:00.000
3 2011-12-30 00:01:00.000
4 2011-12-31 00:00:00.000
5 2011-12-31 00:01:00.000


In fact, I rarely use it at all because of this common mis-perception about the border exclusivity in other developers.



Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.