Thursday, March 05, 2009

Do you have a datetime T-SQL 'BETWEEN AND' bug??

Create a table with a datetime column

create table #t (col1 datetime)

add couple of rows with date and time values

insert into #t values ('1/mar/2009 08:00')
insert into #t values ('1/mar/2009 09:00')
insert into #t values ('1/mar/2009 10:00')

get the rows

select * from #t
where col1 between '1/mar/2009' and '1/mar/2009'

and guess what?
(0 row(s) affected)

Why? You’re trying to select a date without a time and that's treated as 00:00 midnight
and these rows do not exist in the table

Three ways to solve:

append a time value to the sql string:

select * from #t
where col1 between '1/mar/2009 00:00:00' and '1/mar/2009 23:59:59'
(3 row(s) affected)

no go, too much work -specially with variables- and could lead to (more) bugs

select * from #t
where CONVERT(datetime,CONVERT(varchar(20), col1, 112),112) between '1/mar/2009' and '1/mar/2009'
(3 row(s) affected)

uses double convert() T-SQL function to get rid of the time part and switch back to datetime format

no go: if you have a index defined an the column it will not be used by the SQL Server query optimizer because of the convert function. Bad performance!

my absolute thumbs up favorite:

select * from #t
where col1 >= '1/mar/2009' and col1 < dateadd(d,1,'1/mar/2009')
(3 row(s) affected)

GO, this construction has served me well over the years: date/time values are handled correctly,
indexes are used and no hassle with convert() leading to good performance and good maintainable T-SQL code!

have fun

Bookmark and Share