Mike Blamires

There isn't a function in T-SQL which will round dates for you, here is a function which does just that - specifically I needed a function which would round up or down to the hour (up if > 30 minutes into the hour, down if < 30 minutes).


CREATE FUNCTION dbo.RoundDate ( @toRound as DateTime )
RETURNS DateTime AS
BEGIN

Declare @roundedDateTime DateTime
if (datepart(minute, @toRound) < 30)
begin
set @roundedDateTime = DATEADD(Hour, DATEDIFF(Hour, 0, @toRound), 0)
end
else
begin
set @roundedDateTime = DATEADD(Hour, DATEDIFF(Hour, 1, @toRound), 0)
end

return @roundedDateTime

END


The key to this working is that in the dateadd function the number variable is rounded up. So if you supply 1.75 the fractional part is discarded and it's rounded up to 2.

Taking an example with real values (simplified for the example):

Dateadd(Hour, datediff (Hour, '1900-01-01 01:00:00.000', '2007-04-16 16:10:00.000'), '1900-01-01 00:00:00.000')


• When a date value is instantiated with 1 in the above example it means 1 hour from the SQL base time (the SQL epoch if you will).
• The nested datediff function return the following, 940474.80 (approx!), in hours
• The .80 is dropped and the time is then rounded up to 940475
• The whole function now looks like this datediff(hour, 940475, 0) (remember our 0 means the 'SQL epoch')
• This then adds 94075 hours to 0 (or '1900-01-01 01:00:00.000') which leaves us with ... '2007-04-16 17:00:00.000' our time rounded up.

I think it's pretty safe to say that there are a number of ways to do this particular task, I would like to hear them.