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 about how to create a function to round up time to nearest minute interval.
Here is a very simple example of how one can do the same.
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 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.roundtime('17:29',30)
SELECT dbo.roundtime(GETDATE(),5)
SELECT dbo.roundtime('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 (https://blog.sqlauthority.com)
