Ever needed to round an arbitrary DATETIME to some specified increment (i.e. 5, 10 or 15 minutes)? This function can come in handy for such a task.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 | /***************************************************************************** Purpose: Round datetime to nearest X minute increment Description: This script creates a function to round time to the nearest X minute increment. Works with any increment within a day (@min =< 1440) Author: Taylor Gerring Date: 2007-12-17 Notes: On very rare occasions the result may produce a small rounding error (i.e. 59.997). This appears to be a limitation on how SQL handles. conversion. Since the intention is to round anyway, we could certainly accomodate this condition, but I've omitted that for the time being. Test Cases: select dbo.fn_RoundTimeToMinIncrement('2007-12-17 02:14:49.646', 5) [5] , dbo.fn_RoundTimeToMinIncrement('2007-12-17 02:14:49.646', 10) [10] , dbo.fn_RoundTimeToMinIncrement('2007-12-17 02:14:49.646', 15) [15] , dbo.fn_RoundTimeToMinIncrement('2007-12-17 02:14:49.646', 30) [30] , dbo.fn_RoundTimeToMinIncrement('2007-12-17 02:14:49.646', 60) [60] , dbo.fn_RoundTimeToMinIncrement('2007-12-17 03:14:49.646', 120) [120] , dbo.fn_RoundTimeToMinIncrement('2007-12-17 13:14:49.646', 1440) [1440] *****************************************************************************/ create function dbo.fn_RoundTimeToMinIncrement ( @time datetime , @min smallint ) returns datetime as begin /* Ensure minutes is a positive number */ set @min = abs(@min) if @min > 1440 set @min = 1440 /* We are going to separate the DATE and TIME parts, do some math and add them together */ return /* Here's the DATE part */ dateadd(day, 0, datediff(day, 0, @time)) /* And now the TIME math */ + cast(round(cast(cast(convert(varchar(12), @time, 14) as datetime) as float) * 1440.0 / @min, 0) / (1440.0 / @min) as datetime) end go |

2 Responses to Round DATETIME to nearest X minute increment
Gerhard Schmeusser
March 28th, 2010 at 2:30 am
The mentioned rounding error occured very often when I read data from a linked server. I therefore implemented a different solution:
create function dbo.fnrounddatetominutes
(
@date smalldatetime — rounds to minutes
, @minutesBoundary smallint — must be between 1 and 60
)
returns smalldatetime
as
begin
declare @rest int
set @rest = datepart(minute, @date) % @minutesBoundary
return
case
when @rest = 0 then @date
when @rest < ((@minutesBoundary + 1) / 2) then dateadd(minute, -@rest, @date)
else dateadd(minute, @minutesBoundary-@rest, @date)
end
end
go
Rob Owen
May 23rd, 2011 at 6:39 pm
An error is caused by float. Try this on the time 15:12 for example.
I changed the convert function to
select dateadd(day, 0, datediff(day, 0, @time))
+ convert(datetime, convert(decimal(12, 7), (round(cast(cast(convert(varchar(12), @time, 14) as datetime) as float) * 1440.0 / @min, 0) / (1440.0 / @min))))