Thanks,

The script i posted here was a sample, the range table will be used by multiple SP's each querying for a diffrent groups.

yes i did and got logical reads 10, now its back to 5!!!

is there any other way to write the same query?

Thanks frank,

To conclude this should work.

1)Remove the time part

update hdr

set transdate=convert(datetime,convert(char(10),transdate,101))

2)(with convert function removed)

--value passed to parameter in ymd format

no dateformat option set

select * from hdr where

transdate between @fromdate and...

Adding + 1 to the format is an option will consider if all else fails.

The time value will be removed from the transdate field. Please read my first post and...

Reason for using convert

The application has more than 100 reports, we are using crystal reports with VB as front end, all reports will have to be changed to have datetime...

set

objMyConnection.CommandTimeout =0 for  an infinite timeout.

Try to run the query on the server and see why is it taking such a long...