Rounding Datetime

  • Got a silly requirement as part of a larger report.

    Basically, I need to round a DATETIME down to a specific interval.

    DECLARE @TABLE AS TABLE(thedate DATETIME)

    INSERT INTO @TABLE

    SELECT '2011-06-13 07:59:12'

    UNION ALL SELECT '2011-06-13 07:48:15'

    UNION ALL SELECT '2011-06-13 08:00:00'

    UNION ALL SELECT '2011-06-13 07:45:01'

    UNION ALL SELECT '2011-06-13 08:11:11'

    UNION ALL SELECT '2011-06-13 07:48:15'

    For example, if the interval was 1 minute then I would be able to do this: -

    SELECT DATEADD(MINUTE,DATEDIFF(MINUTE, 0, thedate),0)

    FROM @TABLE

    I'm struggling with changing this for specific intervals.

    So for an interval of 15, with the table I have given above the expected results are: -

    2011-06-13 07:59:12 2011-06-13 07:45:00

    2011-06-13 07:48:15 2011-06-13 07:45:00

    2011-06-13 08:00:00 2011-06-13 08:00:00

    2011-06-13 07:45:01 2011-06-13 07:45:00

    2011-06-13 08:11:11 2011-06-13 08:00:00

    2011-06-13 07:48:15 2011-06-13 07:45:00

    Help is always appreciated 🙂


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • D'OH!

    Was being stupid. The answer is this: -

    SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, 0, thedate)/15 * 15, 0)

    FROM @TABLE


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply