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:
1.
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
2.
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!
3.
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
