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
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'
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.
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.
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:
But surely anything that happens after midnight on 31/12 is on the 1st Jan?
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'
Anonymous - that would of course also work but IMO is unwieldy.
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.
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.
Post a Comment