Record selection based on datetime field

[balachandar]

Hi,
I would like to select the records from a table based on A field say workingdate (datetime).I need to fetch records either for a particular date,or for a date range and so on. For a particular date record selection I used

select *
from table
where
cast(working_date as char(11))=cast(getdate() as char(11))

this works fine. But as the data grows, the search is inefficient and it slows down the application drastically.then i tried the following code
select *
from table
where
working_date between '20010521 00:00:00.000' and
'20010521 23:59:59.997'
with whatever testing done till now, the code gives the correct results.can you
Please weather this will be reliable always
or Please suggest a method to select records based on date in an efficient way (the table has almost 2.7 lakh records.)

 

[tlbroadbent]

Suggestion: Remove the cast function from the first query. That is the reason for the slowdown. Recode as

select * from table
where working_date = cast(getdate() as char(11))

You should see significant improvement if working_date is indexed.


Terry

;-) USER, n.: The word computer professionals use when they mean "idiot." -Dave Barry

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[balachandar]

Thanks terry
I tried that it is not fetching all records. Out of 936 records it gives you 115 records only. We want the records
having the datetime starting with say '2001/05/21'(for example) it should completely ignore the time part

Waiting for your reply
thanks and regards
Balachandar.g

 

[tlbroadbent]

I see. Your datetime field contains times also. In that case, use the range test. You may want to use something like this.

select * from table
where working_date >= cast(getdate() as char(11))
And working_date < cast(dateadd(day,1,getdate()) as char(11)) Terry

;-) USER, n.: The word computer professionals use when they mean &quot;idiot.&quot; -Dave Barry

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm