SQL SERVER – Function to Round Up Time to Nearest Minute Interval

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.

Solarwinds

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.

SQL SERVER - Function to Round Up Time to Nearest Minute Interval roundtime

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)

Solarwinds
, , ,
Previous Post
SQL SERVER – Copy Data from One Table to Another Table – SQL in Sixty Seconds #031 – Video
Next Post
SQL SERVER – Beginning New Weekly Series – Memory Lane – #001

Related Posts

25 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

    Reply
  • Its very helpfull function!
    Thanks

    Reply
  • 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)

    Reply
    • Alternately, if you don’t need the milliseconds portion, you could alter the function to return a SMALLDATETIME rather than a DATETIME.

      Reply
  • thanks for sharing this to me

    Reply
  • Excellent,Harsh.

    Reply
  • How would you modify this function to always round up, and not up or down to the closest x mins?

    Reply
  • Cathy,can you give two example and expected answer you are looking for ?

    Reply
  • 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)

    Reply
    • 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

      Reply
      • 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….

    Reply
  • 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?

    Reply
    • 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”

      Reply
      • 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..

    Reply
  • 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.

    Reply
  • correction:
    DecVal = Floor((TIME_TO_SEC(@TIME )+( DivisorVal – 1 ) / DivisorVal ) * @RoundToMin / 60

    Reply
  • 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

    Reply
  • Travis Whidden
    July 19, 2018 3:25 am

    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

    Reply

Leave a Reply

Menu