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

4 comments on “Round DATETIME to nearest X minute increment”

1. Tom Terepka on August 9, 2012 at 7:10 am said:

   Please disregard request re supplementing fnrounddatetominutes; as the code is self-explanatory (duh). I admittedly had not used / re-examined the code for awhile prior to request.
2. Tom Terepka on August 8, 2012 at 8:44 am said:

   I have found the fnrounddatetominutes solution flawless for rounding up / down @date values. I would very much appreciate if you could supplement your existing solution and provide the solutions for always:

   Rounding up @value
   Rounding down @value

   For example:

   Fnrounddatetominutes Round @date up / down
   Fnrounddatetominutesup Round @date up
   Fnrounddatetominutesdown Round @date down

   The result would be the ability to

   Round up / down (already provided)
   Round up (please provide)
   Round down (please provide)

   Thank you
3. Rob Owen on May 23, 2011 at 6:39 pm said:

   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))))
4. Gerhard Schmeusser on March 28, 2010 at 2:30 am said:

   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