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.
where testdec between '1' and '2'
And the long form equivalent
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.
where testdate between '12/30/2011' and '12/31/2011'
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:
where testdate between '12/30/2011' and '1/1/2012'
where testdate >= '12/30/2011' and testdate <= '1/1/2012'
id testdate
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.
