Round DATETIME to nearest X minute increment

In: SQL Server
Written by: Taylor Gerring

15 Aug 2008

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.

/*****************************************************************************
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

Tags: DATETIME, HOWTO, SQL Server, T-SQL

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

Round DATETIME to nearest X minute increment

2 Responses to Round DATETIME to nearest X minute increment

Comment Form

@TaylorGerring

Blogroll

Self