August 29, 2005 at 11:10 pm
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.
August 29, 2005 at 11:47 pm
There is no such function available (to the best of my knowledge) . You will be required to write one.
Amit Lohia
August 30, 2005 at 7:09 am
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.
August 30, 2005 at 7:27 am
Is this a copy/paste from a much older thread Farrell??
August 30, 2005 at 7:51 am
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.
August 31, 2005 at 2:16 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy