How BETWEEN is inclusive

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.

SQL Server Best Practices for DMV's - Houston Tech Fest 2012

It is a pleasure meeting everyone today at Houston Tech Fest!  The new facility is miles away - literally and figuratively - from the previous Houston Tech Fest facility.

Here's a link below for downloading the presentation files and sample scripts from my presentation on "SQL Server Best Practices for DMV's" at Houston Tech Fest 2012 at the Reliant Center.

Download the .zip file here.