I've run into some interesting issues when selecting records based on the date portion. Basically I want to select the max date from a group of records between two dates, using only the date portion. The date field is indexed and I want to avoid having a table scan. I've created a sample script that shows some of the issues...

1. I've attempted to append 00:00:00 and 23:59:59 to the strings, this doesn't work.
2. I've attempted various convert functions on the datetime field in the table, this works but causes a table scan
3. the only work around i've found is to subtract one day and add another.

Play around with this script and you may see some surprising results. I'm sure these are by design and that i'm just not 100% up to speed on datetime comparisons.

Does anyone know the MOST efficent way to do this ?


----------- Sample script showing date select issues
declare @strStartDate varchar(10)
declare @strEndDate Varchar(10)

set @strStartDate = '10/14/2002'
set @strEndDate = '10/15/2002'


set @strStartDate = convert(varchar(10),dateadd(day,-1,Convert(datetime,@strStartDate)),101)
set @strEndDate = convert(varchar(10),dateadd(day,1,Convert(datetime,@strEndDate)),101)

select @strStartDate

create table #tempHold (primeKey int,checkedAt datetime)

insert into #tempHold values(1,'10/15/2002 01:00:00.00')
insert into #tempHold values(2,'10/23/2002 11:00:00.00')
insert into #tempHold values(3,'11/18/2002 11:00:00.00')
insert into #tempHold values(4,'11/18/2002 20:00:00.00')
insert into #tempHold values(5,'11/19/2002 23:00:00.00')

select * from #tempHold

Select max(a.checkedAt) as maxDate from #tempHold a where
@strEndDate
a.checkedAt >= @strstartDate and a.CheckedAt <= @strEndDAte
-- causes a table scan...
--CONVERT(VARCHAR(10), a.checkedAt, 101) Between @strStartDate and

drop table #tempHold