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)
26 Comments. Leave new
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)
Alternately, if you don’t need the milliseconds portion, you could alter the function to return a SMALLDATETIME rather than a DATETIME.
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)
Hi Cathy. Please see my reply to Dave a few posts down. :)
Cathy,can you give two example and expected answer you are looking for ?
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)
This isn’t actually a true “RoundUp” Function. It works like standard rounding, going up or down depending on which is closest. If you want to always round up (or down), you would need to define separate functions, and change the slightly like so:
CREATE FUNCTION [dbo].[RoundTimeDown] (@Time DATETIME, @RoundToMin INT)
RETURNS DATETIME
AS
BEGIN
RETURN FLOOR(CAST(CAST(CONVERT(VARCHAR,@Time,121) AS DATETIME) AS FLOAT(53)) * (1440/@RoundToMin))/(1440/@RoundToMin)
END
GO
CREATE FUNCTION [dbo].[RoundTimeUp] (@Time DATETIME, @RoundToMin INT)
RETURNS DATETIME
AS
BEGIN
RETURN CEILING(CAST(CAST(CONVERT(VARCHAR,@Time,121) AS DATETIME) AS FLOAT(53)) * (1440/@RoundToMin))/(1440/@RoundToMin)
END
GO
Thanks for your contribution @Night1505
the round time up function works most of the time however there are a few cases where it doesn’t , for example
select dbo.[RoundTimeUp](’12:57′,15)
select dbo.[RoundTimeUp](’14:16′,15)
any idea why these return 12:59 and 14:29 instead of the 13:00 and 14:30 ??
Hi,
I am passing month and year from a drop down to procedure
and want to check whether the date from table is lesser than then the passed month and year.
For Eg:
Passed Year:2008
Passed Month:12
From table i get the date value as “Dec 11 2009 12:00AM” . To check this date value is <= passed month and year.
I need it urgent. Kindly help….
This function throws different time roundup than usual for 75min roundup case,
I tried with “SELECT dbo.roundtime (‘2012-11-02 09:27:07.000’,75)”, I got the result “2012-11-02 08:51:00” and I tried the same input with excel for eg. I gave input as “2012-11-02 09:27:07.000” in A1 cell and used this formula “=MROUND(A1, TIME(1,15,0))” and I got output as “11/2/2012 10:00”. So my reqirement is that how to write the sql query in sql server to get same output as in excel for this 75min roundup?
there is no equivalent of MRound in T-SQL. I already said in my post
“the value passed here should be between 0 and 60 – if the value is incorrect the results will be incorrect”
I’m getting wrong result for SELECT dbo.roundtime(’00:23:22′,1) . Getting 1900-01-01 00:22:59.997. Please suggest.
Thanks for your reply Pinal, I agree that it works fine between 0 and 60 and besides it also works for 90min. How it will then? I mean that MROUND excel output matches exactly with your function output for 90 min also but not for 75min.. Anyway As you said I understand that there is no MROUND equivalent in T-SQL but I want to accomplish a task that both Excel’s 75min roundup output and sql server’s 75min roundup output should be same for the date time inputs, Is there any query for this task in T-SQL?
Thanks in advance..
you have to write custom function; nothing inbuilt.
1 – I’m a noob in sql so don’t laugh too hard and feel free to suggest where this is wrong – but it works for me rounding up time to a decimal value ( e.g. endtime – start time, rounded UP to the nearest .25 of an hour. in my example below)
But it may have applicability to the above question. For me Diff is a time value found by End-Start but should work on any time value.
`DecVal`= Floor((TIME_TO_SEC(Diff)+899)/900)*.25 WHERE 1
By replacing parts of it it should work generically
PARAMS: @Time @RoundToMin
DECLARE DivisorVal INT DEFAULT 0 // A Divsor used in the equation three times
DECLARE DecVal DECIMAL DEFAULT 0 // Decimal Value of the time
DivisorVal = @RoundToMin * 60 // Minutes to seconds
DecVal = Floor((TIME_TO_SEC(@TIME )+( DivisorVal – 1 ) / DivisorVal ) * DivisorVal
return SEC_TO_TIME(DecVal) // makes the decimal time a time value again.
correction:
DecVal = Floor((TIME_TO_SEC(@TIME )+( DivisorVal – 1 ) / DivisorVal ) * @RoundToMin / 60
If you use smalldatetime its more precise :)
ALTER FUNCTION [dbo].[RoundTime2] (@Time smalldatetime, @RoundToMin INT)
RETURNS smalldatetime
AS
BEGIN
RETURN CEILING(CAST(CAST(CONVERT(VARCHAR,
@Time,121) AS datetime) AS FLOAT) * (1440/@RoundToMin))/(1440/@RoundToMin)
END
Very interesting. Thanks for sharing.
This was a great lead, but the problem with the floating point rounding was a real issue for me. I was able to overcome that, and also support a function that allows you specificy if you want rounding floor, ceiling, and general rounding. Here is the code for those that come across it. In this, you can round up to 24 hours, at any numer of seconds you want. 5 minutes, 75 minutes, 2 hours, etc. If you need more than 24 hours, just adjust the DateDiff and remove some days from it.
CREATE FUNCTION [dbo].[DateTimeRound]
(
@dateTime DateTime,
@seconds int,
@roundOption int = 0
)
RETURNS DateTime
AS
BEGIN
RETURN CASE @roundOption
WHEN 1 THEN
DATEADD
(
SECOND,
CEILING(DATEDIFF(SECOND, CONVERT(DATETIME,CONVERT(DATE, @dateTime)), @dateTime) / @seconds) * @seconds,
CONVERT(DATETIME,CONVERT(DATE, @dateTime))
)
WHEN -1 THEN
DATEADD
(
SECOND,
FLOOR(DATEDIFF(SECOND, CONVERT(DATETIME,CONVERT(DATE, @dateTime)), @dateTime) / @seconds) * @seconds,
CONVERT(DATETIME,CONVERT(DATE, @dateTime))
)
ELSE
DATEADD
(
SECOND,
ROUND(DATEDIFF(SECOND, CONVERT(DATETIME,CONVERT(DATE, @dateTime)), @dateTime) / @seconds,0) * @seconds,
CONVERT(DATETIME,CONVERT(DATE, @dateTime))
)
END
END
GO
Many Many thanks.
I really was in need of this