T-SQL: Round to nearest 15 minute interval
SqlSql ServerDatetimeSql Problem Overview
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 datetime
s.
Sql Solutions
Solution 1 - Sql
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 - Sql
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 - Sql
This was answered here https://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, @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 - Sql
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 244, the number of quarters in a day. Round to the nearest multiple of quarters with floor(), and then divide by 244 to convert back to normal time.
Solution 5 - Sql
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.
Solution 6 - Sql
Okay easiest way:
convert the minutes to a decimal number by dividing by 60.
8/60 = 0.1333333333333333
multiply by 4
0.1333333333333333 * 4 = 0.5333333333333333
Round the product:
Round(0.5333333333333333,0) = 1
divide the round number by 4
1/4 = 0.25 = 15 minutes
if you want the minutes just multiply it by 60
0.25*60 = 15
Give a man a fish....
Solution 7 - Sql
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))
Solution 8 - Sql
DECLARE @t time ='00:51:00.000'
DECLARE @m int = DATEPART(MI,@t)%15
-- 2008
SELECT DATEADD(mi,CASE WHEN @m >=8 THEN 15-@m ELSE -1*@m END,@t)
-- 2012
SELECT DATEADD(mi,IIF(@m >=8,15-@m,-1*@m),@t)
Solution 9 - Sql
--This is my favorite way to round time
DECLARE @Time DATETIME = GETDATE()
,@RoundInterval INT = 30 --in minutes, needs to be a number that can be divided evenly into 60
,@RoundDirection INT = 2 --0 is down to the last interval, 1 is to the nearest interval, 2 is up to the next interval
SELECT DATEADD(MINUTE,DATEDIFF(MINUTE,0,DATEADD(SECOND,30*@RoundDirection*@RoundInterval,@Time))/@RoundInterval*@RoundInterval,0)
Solution 10 - Sql
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'
Solution 11 - Sql
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.
Solution 12 - Sql
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
Solution 13 - Sql
To set block in 15 minutes:
CREATE FUNCTION RoundQuarterHour (
@dt DATETIME
) RETURNS DATETIME
AS
BEGIN
DECLARE @date DATETIME
SET @date = CONVERT(varchar(16),@dt,121) --Sin segundos, ni milisegundos
RETURN DATEADD(MINUTE,(DATEPART(MINUTE,@date) % 15)*-1, @date)
END
PRINT dbo.RoundQuarterHour('2011/01/01 18:00:07') --Jan 1 2011 6:00PM
PRINT dbo.RoundQuarterHour('2011/01/01 18:01:07') --Jan 1 2011 6:00PM
PRINT dbo.RoundQuarterHour('2011/01/01 18:13:07') --Jan 1 2011 6:00PM
PRINT dbo.RoundQuarterHour('2011/01/01 18:14:07') --Jan 1 2011 6:00PM
PRINT dbo.RoundQuarterHour('2011/01/01 18:15:07') --Jan 1 2011 6:15PM
PRINT dbo.RoundQuarterHour('2011/01/01 18:16:07') --Jan 1 2011 6:15PM
Solution 14 - Sql
This will round to the nearest 15 minutes. You can modify @ROUND to the interval of your choice.
Declare @Dt DateTime = '2016-01-01 14:38:00'
DECLARE @ROUND int = 15;
SELECT
CASE WHEN (DATEPART(MINUTE, @Dt) % @ROUND) * 60 + DATEPART(SECOND, @Dt) < (30 * @ROUND)
THEN DATEADD(minute, datediff(minute,0, @Dt) / @ROUND * @ROUND, 0)
ELSE DATEADD(minute, (DATEDIFF(minute,0, @Dt) / @ROUND * @ROUND) + @ROUND, 0)
END
Solution 15 - Sql
Premise breaks down to figuring out what increment you want, what's that as a percent of 60 minutes...then figure out the needed number of increments to get there...take the INT value (this chops off remainders) and there you have it, a simple function to round Up or down to the closest increment.
Simple function:
ALTER FUNCTION [dbo].[RoundOffDateTime]
(
@IncDate DATETIME,
@Increment INT
)
RETURNS SMALLDATETIME
AS
BEGIN
DECLARE @IncrementPercent DECIMAL(2,2) = CAST(@Increment as decimal)/60
DECLARE @IncMinutes REAL = ROUND(CAST(DATEPART(mi,@IncDate) as decimal)/CAST(@Increment as decimal),0)
DECLARE @MinutesNeeded INT = CAST(@IncMinutes * @Increment as INT)
RETURN CAST(DATEADD(mi,@MinutesNeeded,DATEADD(ss,-DATEPART(ss,@IncDate),DATEADD(mi,-DATEPART(mi,@IncDate),@IncDate))) as smalldatetime)
END
Solution 16 - Sql
DECLARE @Date DATETIME = GETDATE()
SELECT @Date
, DATEADD(ms, 900000 - DATEDIFF(ms, CAST(@Date AS DATE), @Date) % 900000, @Date)