Wednesday, September 26, 2012

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.


5 comments:

Unknown said...

But surely anything that happens after midnight on 31/12 is on the 1st Jan?

Anonymous said...

Or add a time to the second date in the between:

select * from datebetweentesting where testdate between '12/30/2011' and '12/31/2011 23:59:59.999'

w said...

Anonymous - that would of course also work but IMO is unwieldy.

Anonymous said...

this has less to do with the BETWEEN operator and more to do with implicit conversion. Even though you typed BETWEEN 12/1/2011 and 12/31/2011 (which you meant to say if it happened during December of 2011 I care about it.) Your values were "implicitly" converted to two 4 byte integers representing the dateTIME values of BETWEEN 12/1/2011 00:00:00.000 AND 12/31/2011 00:00:00.000. Which means what you actually said was "if it happened in December of 2011 but not on the last day of the year I care about it."

What you really meant to say was BETWEEN 12/1/2011 00:00:00.000 AND 12/31/2011 23:59:59.997 (note the MS, not explained here there are tons of articles covering 3ms cutoff in SQL)
or (less performant) MONTH(YourDateColumn) = 12 AND YEAR(YourDateColumn) = 2011.

My advice to developers would not be to STOP using between for dates, but rather to make sure that if your column cares about time, your where clause should too.

If I see a WHERE clause that says BETWEEN 12/1/2011 and 1/31/2011, I expect the column to never include any time other than 00:00:00.000 (maybe even constrained).

So, as a T-SQL developer, whether you are using >= / <= or BETWEEN. If your data cares about the time, make sure your code does too.

Anonymous said...

This really depends if your column is a DATETIME rather than a DATE column. In the case of a DATETIME column the need to use CAST(DATECOLUMN as DATE) will make the query inclusive.