[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 193
  • Last Modified:

Dynamic Query Based On Time

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?
0
sainiak
Asked:
sainiak
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so you are so close ...

WHERE Timestamp >= dateadd( minute, -datepart(minute, getdate()), dateadd ( hour, -2, getdate() ))
 and Timestamp < dateadd( minute, -datepart(minute, getdate()), dateadd ( hour, -1, getdate() ))

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ok, that was only setting the minute to 0...
here to also set the second to 0:

WHERE Timestamp >= dateadd( second, -datepart(second, getdate()), dateadd( minute, -datepart(minute, getdate()), dateadd ( hour, -2, getdate() )))
 and Timestamp < dateadd( second, -datepart(second, getdate()), dateadd( minute, -datepart(minute, getdate()), dateadd ( hour, -1, getdate() )))

0
 
Patrick MatthewsCommented:
WHERE Timestamp >= DATEADD(hh, DATEPART(hh, GETDATE()) - 1, CONVERT(datetime, CONVERT(varchar, GETDATE(), 102)) AND
    TimeStamp < DATEADD(hh, DATEPART(hh, GETDATE()), CONVERT(datetime, CONVERT(varchar, GETDATE(), 102))
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now