Round DATETIME to nearest X minute increment

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

1 Response 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

Idea Excursion

Round DATETIME to nearest X minute increment

1 Response to Round DATETIME to nearest X minute increment

Comment Form

Categories

Archives

Blogroll

Other