T-SQL: Round to nearest 15 minute interval

Solution 1:

I am currently using a dateadd / datediff variant with a zero (0) date for this. No Casting required:

select dateadd(minute, datediff(minute,0,GETDATE()) / 15 * 15, 0)

GETDATE() is whatever your datetime is.

This will work for dates at least up to the year 5500 before the datediff failes because of an overflow. However if you try to use second accuracy, above will fail right away.

Using another fixed date, like '2009-01-01', or Today's date (warning, more ugly SQL) will fix that. A future date will also work. As long as it has a time part of 00:00:00 you can base another datetime on it.

for example: round to the nearest 30 seconds:

select dateadd(second, round(datediff(second, '2010-01-01', GETDATE()) / 30.0, 0) * 30, '2010-01-01');

Solution 2:

I know this is a old post but wanted to share my answer. This builds on @hbrowser response. Here is what I've come up with. This will round up or down to the nearest 15 minutes.

SELECT DATEADD(MINUTE, ROUND(DATEDIFF(MINUTE, 0, GETDATE()) / 15.0, 0) * 15, 0);

By doing this logic inline, rather than inside a user defined function, over large recordsets you should experience greater performance.

You can change the way rounding occurs by swapping the ROUND function to use FLOOR or CAST expr AS INT to always round down or use CEILING to always round up.

Your individual use case will determine what style of rounding you may need to use.

The following script can be used to observe the differences offered by the different rounding techniques:

NOTE: to simplify the output each result has been casted to TIME(0), this is only done to simplify the output for this particular example.

DECLARE @SequenceStart SmallDateTime = CAST(GETDATE() AS Date); 
DECLARE @SequenceEnd SmallDateTime = DateAdd(HOUR, 2, @SequenceStart); -- Recursive CTEs should always have an upper limit
DECLARE @SequenceIntMins INT = 5; -- increment by 5 to show the difference with rounding
WITH TimeSequence([Time]) as
(
    SELECT @SequenceStart as [Time]
    UNION ALL
    SELECT DateAdd(MINUTE, 5, [Time]) FROM TimeSequence 
    WHERE [Time] <= @SequenceEnd
)
    SELECT [Time] = Cast([Time] as TIME(0))
    , Rounded = CAST(DATEADD(MINUTE, ROUND(DATEDIFF(MINUTE, 0, [Time]) / 15.0, 0) * 15, 0) as TIME(0))
    , Casted = CAST(DATEADD(MINUTE, CAST(DATEDIFF(MINUTE, 0, [Time]) / 15.0 AS INT) * 15, 0) as TIME(0))
    , Floored = CAST(DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, 0, [Time]) / 15.0) * 15, 0) as TIME(0))
    , Ceilinged = CAST(DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, 0, [Time]) / 15.0) * 15, 0) as TIME(0))
FROM TimeSequence OPTION ( MaxRecursion 1000);
-- MaxRecursion may be neccessary if you change the interval or end of the sequence

Time        Rounded     Casted      Floored     Ceilinged
00:00:00    00:00:00    00:00:00    00:00:00    00:00:00
00:05:00    00:00:00    00:00:00    00:00:00    00:15:00
00:10:00    00:15:00    00:00:00    00:00:00    00:15:00
00:15:00    00:15:00    00:15:00    00:15:00    00:15:00
00:20:00    00:15:00    00:15:00    00:15:00    00:30:00
00:25:00    00:30:00    00:15:00    00:15:00    00:30:00
00:30:00    00:30:00    00:30:00    00:30:00    00:30:00
00:35:00    00:30:00    00:30:00    00:30:00    00:45:00
00:40:00    00:45:00    00:30:00    00:30:00    00:45:00
00:45:00    00:45:00    00:45:00    00:45:00    00:45:00
00:50:00    00:45:00    00:45:00    00:45:00    01:00:00
00:55:00    01:00:00    00:45:00    00:45:00    01:00:00
01:00:00    01:00:00    01:00:00    01:00:00    01:00:00
01:05:00    01:00:00    01:00:00    01:00:00    01:15:00

Solution 3:

This was answered here How to Round a Time in T-SQL and i think it should work for you to.

CREATE FUNCTION [dbo].[RoundTime] (@Time datetime, @RoundTo float) RETURNS datetime
AS
BEGIN
    DECLARE @RoundedTime smalldatetime, @Multiplier float

    SET @Multiplier = 24.0 / @RoundTo

    SET @RoundedTime= ROUND(CAST(CAST(CONVERT(varchar, @Time, 121) AS datetime) AS float) * @Multiplier, 0) / @Multiplier

    RETURN @RoundedTime
END

-- Usage    
SELECT dbo.RoundTime('13:15', 0.5)

Solution 4:

You can round a date to the nearest quarter like:

cast(floor(cast(getdate() as float(53))*24*4)/(24*4) as datetime)

Casting datetime to double precesion to avoid overflows, double = float(53). Multiply by 24*4, the number of quarters in a day. Round to the nearest multiple of quarters with floor(), and then divide by 24*4 to convert back to normal time.

Solution 5:

Tried Andomar's answer and there was rounding issues at 30 and 00 - so a few tweaks and this works perfectly:

cast(round(floor(cast(getdate() as float(53))*24*4)/(24*4),5) as smalldatetime)

This will show the last 15 minute increment, not the nearest, i.e. it won't go forward which is exactly what I needed.