Rounding Time intervals
-
Is there a way to round The Time portion of a DateTime to the nearest 15 Interval.
Im trying to generate a report of users timing in but i need to round to the nearest 15 min
Thanks in advance.
-
There is no such function available (to the best of my knowledge) . You will be required to write one.
Kindest Regards,Amit Lohia
-
I got this from Frank Kalis, (and because I am so thick headed he had to explain it to me more than once...). It is a VERY slick way to get data based upon various date ranges...
I altered the first SELECT, (because I understood it better) to give me the results of modulo, the original date, and the DateByFiveMinutes... Hope this helps, (Frank is rather amazing...).
--Farrell, you can either use '19000101' or 0.
CREATE TABLE #time( [ID] INT IDENTITY,
dt DATETIME,
ReasonID INT)
INSERT INTO #time
SELECT '20050427 10:50:00', NULL -- 1
UNION ALL
SELECT '20050427 10:51:00', NULL -- 2
UNION ALL
SELECT '20050427 10:52:00', NULL -- 3
UNION ALL
SELECT '20050427 10:53:00', 20206 -- 4
UNION ALL
SELECT '20050427 10:54:00', 20206 -- 5
UNION ALL
SELECT '20050427 10:55:00', NULL -- 6
UNION ALL
SELECT '20050427 10:56:00', 20212 -- 7
SELECT [ID],
DATEADD( minute, -DATEPART( minute, dt) % 5, dt) AS DateByFiveMinutes,
-DATEPART( minute, dt) % 5 AS Modulo,
dt
FROM #time
-- Both will produce the same result. The former is interpreted by SQL Server as a date by converting the string to a DATETIME.
-- The latter is converted from an INT to DATETIME. In both cases it's the server's base date.
-- Basically it's the same trick you can use to set the time in a DATETIME column to midnight.
-- This technique works with all the allowed parameters for DATEADD and DATEDIFF.
-- Here it strips off the seconds and milliseconds.
SELECT DATEADD( minute, DATEDIFF( minute, 0, dt) / 5 * 5, 0),
COUNT(*) - SUM( CASE WHEN ReasonID IS NULL THEN 0 ELSE 1 END) AS Running,
COUNT(*) - SUM( CASE WHEN ReasonID IS NOT NULL THEN 0 ELSE 1 END) AS Down
FROM #time
GROUP BY DATEADD( minute, DATEDIFF( minute, 0, dt) / 5 * 5, 0)
DROP TABLE #time
I wasn't born stupid - I had to study.
-
Is this a copy/paste from a much older thread Farrell??
-
Probably most of it. But I had to PM Frank 'cause I did not understand how modulo was working... I am sure we used the same if not similar code from a previous post. The first query includes changes Frank made that may not have been in the original thread so I could understand it better...(hence his notes...).
I wasn't born stupid - I had to study.
-
Try this link for a solution using DATEDIFF with time span. Hope this helps.
http://www.stanford.edu/~bsuter/sql-datecomputations.html
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply