Retrieving last 12/24/36 hrs records

Hi there !!
I am new on this block. Can anyone help me to make sql queries which can retrieve last 12 hrs / last 24 hrs / last 36 hrs records.

I tried to make the logic for the last 3 hrs but yet not able to get the data which i want. I am using MS SQL Server 2000 and ASP.

My table named "campaign" contains the field 'camdate' which is of smalldatetime datatype.

Also, can i am able to retrieve the time from the same field (camdate)? if yes then how ??

Any suggestion is most welcome.

With Thankshi

SELECT *
FROM Table1
where DATEDIFF(hh, camdate, GETDATE()) >= 12

for last 12 hours

regards,
Gautam Vegad

Originally posted by sqlboy
Hi there !!
I am new on this block. Can anyone help me to make sql queries which can retrieve last 12 hrs / last 24 hrs / last 36 hrs records.

I tried to make the logic for the last 3 hrs but yet not able to get the data which i want. I am using MS SQL Server 2000 and ASP.

My table named "campaign" contains the field 'camdate' which is of smalldatetime datatype.

Also, can i am able to retrieve the time from the same field (camdate)? if yes then how ??

Any suggestion is most welcome.

With Thanks|||I think this will run faster, because it should only need to calculate dateadd(hh, -12, GETDATE()) once instead of calculating datediff for every record:

SELECT *
FROM Table1
where camdate >= dateadd(hh, -12, GETDATE())

Try it and see if the execution plan is different.

blindman|||Thanks to both of you..

Being a kid, i am not able to find the difference between dateadd and datediff. Can you put some light on it ? I shall be very greatful to you.|||Well, I ran one test on some data that I have, and the dateadd method was about 30% faster.

As I said, the difference is that the server calculates the result of dateadd once for the entire statement, while it must calculate the result of datediff once for every row.

blindman|||Well...maybe...

But the first one is a stage 2 predicate (non-sargable)..it'll have to do a table (or index) scan..

Blindman's has the ability to use an Index (stage 1, saragable)

And once you start talking volumes, the performance will be noticable...

MOO (well not really)|||Good point. I forgot about the sargableosityness of the two statements.

blindman