Round DATETIME to nearest X minute increment

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
		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 thoughts on “Round DATETIME to nearest X minute increment

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

Leave a Reply

Your email address will not be published. Required fields are marked *

* Copy This Password *

* Type Or Paste Password Here *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Current ye@r *