tags:

views:

2819

answers:

2

I'me looking for a function that would receive a time and would round it to the next/previous hour / half-hour / quarter / minute.

+7  A: 

Try this function

CREATE function [dbo].[RoundTime] (@Time datetime, @RoundTo float)
returns datetime
as
begin
declare @RoundedTime smalldatetime
declare @Multiplier float

set @Multiplier= 24.0/@RoundTo

set @RoundedTime= ROUND(cast(cast(convert(varchar,@Time,114) as datetime) as float) * @Multiplier,0)/@Multiplier
return @RoundedTime
end



select dbo.roundtime('13:15',0.5)

The 1st param is the time to be rounded and the 2nd will be base on your list (0.5-half hour, 1-one hour, ...)

01
If you use 121 in place of 113 it will get the datepart correct also
vzczc
Sorry, 121 in place of 114
vzczc
Worth nothing that increments must be specified as decimal notation of a fraction of an hour. So if you want it rounded to 5-minute intervals, then that is 1/12 of an hour, so @RoundTo would be .083.
eidylon
A: 

Very nice thanks. I used it in-line to round to 15 minutes

convert(smalldatetime,ROUND(cast(TDatalog.Time as float) * (24/.25),0)/(24/.25)) AS RoundedTime
Kevin