T-SQL: Round to nearest 15 minute interval

Question

What's the best way to round a 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?

Thanks,

James

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.

Answer 1

This was answered here http://stackoverflow.com/questions/249794/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 declare @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

select dbo.roundtime('13:15',0.5)

Answer 2

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.

Answer 3

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'

Answer 4

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.

Answer 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.

Answer 6

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');

Answer 7

how about this one? (variable added for readability)

create function dbo.FloorTimeToQuarters ( @dt as datetime ) RETURNS datetime as

BEGIN

DECLARE @timeAsInt bigint SET @timeAsInt = ( cast( @dt as float ) * 96 ) RETURN DateAdd( hour, @timeAsInt % 96, cast( @timeAsInt / 96 as datetime) )

END

Answer 8

Try this:

Declare @Dt DateTime 
Set @Dt = getDate()

Select DateAdd(minute, 
        15 * ((60 * Datepart(hour, @Dt) + 
     Datepart(Minute, @Dt)+ 
     Case When DatePart(second, @Dt) < 30 
     Then 7 Else 8 End) / 15),
    DateAdd(day, DateDiff(day, 0, @Dt), 0))