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