Previous Hour or X Minutes in TSQL Snapping to Window
In TSQL, how can I can get the previous hour snapping to the hour.
So for example, if it was now 2:33, how would I get fields where CreationDate
(a datetime field) is greater than or equal to 1:00 and less than 2:00.
I would also like to to do this with 10 minute intervals (snapping to the previous :00 - :10 if it were currently :14 for example, or another example, :50-:00 if 开发者_开发问答it were :06).
This would get you the start and end of the current hour:
select dateadd(hour, datepart(hour, getdate()),
dateadd(day, 0, datediff(day, 0, getdate())))
, dateadd(hour, 1+datepart(hour, getdate()),
dateadd(day, 0, datediff(day, 0, getdate())))
Explanation: dateadd(day, 0, datediff(day, 0, getdate()))
gets you the start of today. Then you add the current hour to that.
Along the same lines for the previous 10 minute block:
select dateadd(minute, datepart(minute, getdate()) / 10 * 10 - 10,
dateadd(hour, datepart(hour, getdate()),
dateadd(day, 0, datediff(day, 0, getdate()))))
, dateadd(minute, datepart(minute, getdate()) / 10 * 10,
dateadd(hour, datepart(hour, getdate()),
dateadd(day, 0, datediff(day, 0, getdate()))))
Or another way of writing it:
declare @lowerBound datetime = dateadd(minute, datepart(minute, getdate()) / 10 * 10 - 10,
dateadd(hour, datepart(hour, getdate()),
dateadd(day, 0, datediff(day, 0, getdate()))))
declare @upperBound datetime = dateadd(minute, datepart(minute, getdate()) / 10 * 10,
dateadd(hour, datepart(hour, getdate()),
dateadd(day, 0, datediff(day, 0, getdate()))))
select @lowerBound, @upperBound
PS keep up the good work on Stack Overflow performance!
精彩评论