datediff question

Hello All!

I want to select records that are 24 hours old or older.

Here is my code

SELECT *, DATEDIFF(Hour, Service_Date_Time, getdate())AS Hours

FROM Active_Orders

WHERE (Status_ID = 4)

Not sure how I write the 24 hours part?

Any thoughtS

Thanks!

Rudy

Use a filter expression in the "where" clause.

SELECT *, DATEDIFF(Hour, Service_Date_Time, getdate()) AS Hours

FROM Active_Orders

WHERE (Status_ID = 4) and Service_Date_Time <= DATEADD(Hour, 24, getdate())

AMB

|||

This may work for you:

Code Snippet

SELECT

{ColumnList},

Service_Date_Time

FROM Active_Orders

WHERE ( Status_ID = 4

AND Service_Date_Time >= dateadd( day, -1, getdate() )

)

You want to avoid using a function on the Service_Date_Time field since doing so would eliminate any chance of using indexing on the column.

*NOTE: (Using [ SELECT * ] is not a good idea, and is not in the accepted 'best practices'.)

|||

Hi Arnie,

Good point about not manipulating the column in the filter expression (Mea culpa - I changed my post after reading yours). The OP wants 24 hours old or older.

...

WHERE ( Status_ID = 4

AND Service_Date_Time <= dateadd( day, -1, getdate() )

)

go

AMB

|||

Thanks Alejandro,

I'm glad you caught the mistake -I was thinking 'FOR' the last 24 hours. Your change [ <= ] will catch those MORE than 24 hours.

|||

WOW!

Thanks guys! Great suggestions!

Rudy