Hi Robert,

I would consider most efficient the following code. The SQL Query optimizer would use index if it'll be faster than using scan.

declare @StartDate datetime
declare @EndDate datetime
set @StartDate = CAST('2002/10/14 00:00:00' AS datetime)
-- The actual enddate is '10/15/2002' bu we'll use midnight of the next day.
set @EndDate = CAST('2002/10/16 00:00:00' AS datetime)

Select max(a.checkedAt) as maxDate 
	from #tempHold a 
	where a.checkedAt >= @StartDate and a.checkedAt < @EndDate

>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