SQL SERVER – Function to Round Up Time to Nearest Minutes 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.

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

CREATE FUNCTION [dbo].[RoundTime] (@Time DATETIME, @RoundToMin INT)
ROUND(CAST(CAST(CONVERT(VARCHAR,@Time,121) AS DATETIME) AS FLOAT) * (1440/@RoundToMin),0)/(1440/@RoundToMin)

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.

Click to Download Scripts

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

15 thoughts on “SQL SERVER – Function to Round Up Time to Nearest Minutes Interval

  1. 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


  2. 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)


  3. Pingback: SQL SERVER – Display Datetime in Specific Format – SQL in Sixty Seconds #033 – Video « SQL Server Journey with SQL Authority

  4. 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,

    SET @Dt = CAST(‘2013-01-18 13:34.123′ AS DATETIME);
    SELECT dbo.RoundTime(@dt,15)


  5. 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….


  6. 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”


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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s