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

About these ads

11 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,

    DECLARE @Dt DATETIME;
    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….

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