Rounding Time intervals

Question

Post reply

Rounding Time intervals

Dimitry V

Valued Member

Points: 67
More actions
August 29, 2005 at 11:10 pm

#168577

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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Amit Lohia SSCertifiable Points: 6104 More actions · Answer 1

There is no such function available (to the best of my knowledge) . You will be required to write one.

Kindest Regards,

Amit Lohia

Farrell Keough SSCoach Points: 17414 More actions · Answer 2

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.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Is this a copy/paste from a much older thread Farrell??

Farrell Keough SSCoach Points: 17414 More actions · Answer 4

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.

Gift Peddie SSC Guru Points: 73570 More actions · Answer 5

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