T-SQL: Round to nearest 15 minute interval

user_70489 0
Viewed 106981

What's the best way to round an HH:MM value to the closest 15 minute interval? I don't track seconds so they don't matter.

00:08:00 becomes 00:15:00 
00:07:00 becomes 00:00:00 
01:59:00 becomes 02:00:00

and so on. Is there an elegant, non UDF or Case statement method for doing this?

EDIT: Here's the SQL I'm using to get the above values that I'd like to round:

CONVERT(CHAR(8), DATEADD(n, SUM(DATEDIFF(n, starttime, stoptime)), 0), 108)

starttime and stoptime are SQL datetimes.

datetime sql-server sql
16 Answers

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)
user_315080

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.

user_505520 
create function RoundQuarterHour
(
    @dt datetime
)
returns datetime
as
begin
    declare @result datetime
    declare @mm int
    set @mm=datepart(minute,@dt)
    set @result = dateadd(minute,-@mm + (round(@mm/cast(15 as float),0)*15) , @dt )

    return @result
end
go


           select dbo.RoundQuarterHour('2009-may-5 20:00') , '00'
 union all select dbo.RoundQuarterHour('2009-may-5 20:01') , '01'
 union all select dbo.RoundQuarterHour('2009-may-5 20:07') , '07'
 union all select dbo.RoundQuarterHour('2009-may-5 20:08') , '08'
 union all select dbo.RoundQuarterHour('2009-may-5 20:22') , '22'
 union all select dbo.RoundQuarterHour('2009-may-5 20:23') , '23'
 union all select dbo.RoundQuarterHour('2009-may-5 20:37') , '37'
 union all select dbo.RoundQuarterHour('2009-may-5 20:38') , '38'
 union all select dbo.RoundQuarterHour('2009-may-5 20:52') , '52'
 union all select dbo.RoundQuarterHour('2009-may-5 20:53') , '53'
 union all select dbo.RoundQuarterHour('2009-may-5 20:59') , '59'
feihtthief_3200700

Time rounding in T-SQL is actually very problematic and many times inaccurate.

Years ago, I moved all rounding of times into code vs. using all the extra hub-bub one has to do in T-SQL to make it happen and to happen accurately. Rounding times in code is easier and much more accurate.

If you're stuck in T-SQL and have no supporting code, or don't have access to that code, then follow the examples previously mentioned. Otherwise, I humbly recommend letting code do the work.

user_325320 
    DECLARE   @Date             DATETIME = GETDATE()

    SELECT    @Date
            , DATEADD(ms, 900000 - DATEDIFF(ms, CAST(@Date AS DATE), @Date) % 900000, @Date)
user_57587530
Related