Though I have written more than 2300 blog posts, I always find things which I have not covered earlier in this blog post. Recently I was asked if I have written a function which rounds up or down the time based on the minute interval passed to it. Well, not earlier but it is here today.

Here is a very simple example of how one can do the same.

ALTER 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


Above function needs two values. 1) The time which needs to be rounded up or down. 2) Time in minutes (the value passed here should be between 0 and 60 – if the value is incorrect the results will be incorrect.) Above function can be enhanced by adding functionalities like a) Validation of the parameters passed b) Accepting values like Quarter Hour, Half Hour etc.

Here are few sample examples.

SELECT dbo.roundtime1('17:29',30)
SELECT dbo.roundtime1(GETDATE(),5)
SELECT dbo.roundtime1('2012-11-02 07:27:07.000',15)

When you run above code, it will return following results.

Well, do you have any other way to achieve the same result? If yes, do share it here and I will be glad to share it on blog with due credit.

Reference: Pinal Dave (http://blog.sqlauthority.com)