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.
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
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 (
http://blog.sqlauthority.com
)
It is a very useful function. Refer to the script, may I know what is the purpose of converting datetime to varchar and back to dateime ? i.e. CONVERT(VARCHAR,@Time,121) AS DATETIME
Its very helpfull function!
Thanks
for ,
SELECT dbo.roundtime(GETDATE(),5)
result should come ’2012-11-02 12:35:00.000′, but it came ’2012-11-02 12:34:59.997′
My suggested solution is :
SELECT DATEADD(mi,ROUND(DATEDIFF(mi,0,@Time)*1.0/@RoundToMin,0)*@RoundToMin,0)
thanks for sharing this to me
Excellent,Harsh.
How would you modify this function to always round up, and not up or down to the closest x mins?
select getdate(),dateadd(minute,datediff(minute,0,getdate()),0)
Cathy,can you give two example and expected answer you are looking for ?
Pingback: SQL SERVER – Display Datetime in Specific Format – SQL in Sixty Seconds #033 – Video « SQL Server Journey with SQL Authority
Hi,
I cannot get this to work. If I enter a time of 13:34 with an interval of 15 I thout it would go to 13:45,
DECLARE @Dt DATETIME;
SET @Dt = CAST(’2013-01-18 13:34.123′ AS DATETIME);
SELECT dbo.RoundTime(@dt,15)