Dynamic Query Based On Time
Posted on 2007-07-24
I have a SQL Server query that runs hourly (not always the same minute) with a dynamic where clause. I want to retrieve records from the last "complete" hour (minute:second 00:00 to 59:59).
For example, if the query ran at 16:15, I want the records from 15:00:00.000 to 15:59:59.000.
The best I can do is this:
WHERE Timestamp >= dateadd ( hour, -2, getdate() ) and Timestamp < dateadd ( hour, -1, getdate() )
Which returns the records from 14:15 to 15:15.
Is there a way to set the minute, second, and ms = 0 in the WHEREclause, so no matter when it ran in the hour it would return the previous "complete" hour?