I have a 24 megabyte database in SQL Server 2000.
When I run this request
select * from cdr
where starttime between '2011-05-15 00:00:00.000' and '2011-05-16 00:00:00.000'
and even that
declare @MinDate char(30) ,@MaxDate char(30)
set @MinDate=substring(convert(char,(getdate()-1), 120),1,10)+' 00:00:00.000'
set @MaxDate=substring(convert(char,(getdate()), 120),1,10)+' 00:00:00.000'
select * from cdr
where starttime between '2011-05-15 00:00:00.000' and @MaxDate
it works very fast and returns 3500 records in the first 10 seconds, note that starttimethere is char(30)in the database
But when I run this query, it just returns 32 records in 10 ~ 60 seconds
declare @MinDate char(30), @MaxDate char(30)
set @MinDate = substring(convert(varchar, (getdate()-1), 120),1,10)+' 00:00:00.000'
set @MaxDate = substring(convert(varchar, (getdate()), 120),1,10)+' 00:00:00.000'
select * from cdr
where starttime between @MinDate and @MaxDate
:: @MinDate is 2011-05-15 00: 00: 00.000
Please note that is starttimeindexed in my database
I want to know what is my problem?