SQL Server Round DateTime Up

Question

I need a function that rounds UP to the nearest x mins. I found the one below, but it rounds up or down to the nearest x mins. Based on another value, I would either need to round it UP to the nearest half hour or hour.

CREATE FUNCTION [dbo].[RoundTime] (@Time DATETIME, @RoundToMin INT)
RETURNS DATETIME
AS
BEGIN
RETURN ROUND(CAST(CAST(CONVERT(VARCHAR,@Time,121) AS DATETIME) AS FLOAT) * (1440/@RoundToMin),0)/(1440/@RoundToMin)
END
GO

Answer 1

This changes your function to round up to any number of minutes.

CREATE FUNCTION [dbo].[RoundUpTime] (@Time DATETIME, @RoundToMin INT)
RETURNS DATETIME
AS
BEGIN
return dateadd(mi, - datepart(mi, @time) + (datepart(mi, @time) + @roundToMin - 1) / @roundToMin * @roundToMin, @Time)
END
GO

Note I changed the name to RoundUpTime. Caveat, it works on the MINUTE section only, and ignores seconds and milliseconds.

select dbo.RoundUpTime('20121212 12:30:00.003', 30)
--
2012-12-12 12:30