Rounding Datetime to nearest 15 minutes in SQL Server

Question

I have a problem in SQL Server with rounding datetime. I got datetime in column rec_datetime, but I want to round this datetime in a new column r_datetime, which has to be rounded to nearest 15 min, for the whole column rec_datetime.

Example:

• [2015-11-24 19:06:00.000] - expected result -> [2015-11-24 19:00:00.000]
• [2015-11-24 19:09:00.000] - expected result -> [2015-11-24 19:15:00.000]

Is it possible to round it via select for whole column? Something like :

select round(rec_datetime.......

Answer 1

Round-down, round-nearest & round-up to nearest 15mins

DATEADD( minute, ( DATEDIFF( minute, 0,                                   dateTimeX   ) / 15 ) * 15, 0 ) AS dateTimeRoundDown
DATEADD( minute, ( DATEDIFF( minute, 0, DATEADD( second, ( 15 * 60 ) / 2, dateTimeX ) ) / 15 ) * 15, 0 ) AS dateTimeRoundNearest
DATEADD( minute, ( DATEDIFF( minute, 0, DATEADD( minute,   15           , dateTimeX ) ) / 15 ) * 15, 0 ) AS dateTimeRoundUp  

Round Down

DATEADD( minute, ( DATEDIFF( minute, 0, dateTimeX ) / 15 ) * 15, 0 ) AS dateTimeRoundDown

Get the offset in minutes (number of minutes since the base-date):

DATEDIFF( minute, 0, dateTimeX )

Round-down to 15 minute block by integer dividing:

DATEDIFF( minute, 0, dateTimeX ) / 15 ) * 15

Add the base-date back in minutes:

DATEADD( minute, ( DATEDIFF( minute, 0, dateTimeX ) / 15 ) * 15, 0 )

Round Nearest

DATEADD( minute, ( DATEDIFF( minute, 0, DATEADD( second, ( 15 * 60 ) / 2, dateTimeX ) ) / 15 ) * 15, 0 ) AS dateTimeRoundNear

15 / 2 minutes is added to the offset.

Needs to be in seconds due to the integer division.

Round Up

DATEADD( minute, ( DATEDIFF( minute, 0, DATEADD( minute, 15, dateTimeX ) ) / 15 ) * 15, 0 ) AS dateTimeRoundUp  

15 minutes is added to the offset

Base Date

I generally use a base date of 0 which is the SQL Server 'epoch'

SELECT DATEADD( minute, 0, 0 ) -- '1900-01-01 00:00:00.000'

Because DATEADD() & DATEDIFF() use the SQL Server data type of INT (32 bits) for the parameters, for dates in the very far future, this may cause an overflow.

Using another fixed date, eg '2010-01-01', will avoid the overflow.

The chosen base-date must have a time part of 00:00:00

Using a base-date and integer division, no casting & no floating point operations are required.

Unit Testing

DECLARE @start DATETIME  = '2017-04-20 21:00:00'
DECLARE @end   DATETIME  = '2017-04-20 22:00:00'

;WITH CTE_dateTimes AS
(
    SELECT @start AS dateTimeX
    UNION ALL
    SELECT DATEADD( minute, 1, dateTimeX )
    FROM CTE_dateTimes
    WHERE DATEADD( minute, 1, dateTimeX ) <= @end
)
SELECT dateTimeX,
   DATEADD( minute, ( DATEDIFF( minute, 0,                                   dateTimeX   ) / 15 ) * 15, 0 ) AS dateTimeRoundDown,
   DATEADD( minute, ( DATEDIFF( minute, 0, DATEADD( second, ( 15 * 60 ) / 2, dateTimeX ) ) / 15 ) * 15, 0 ) AS dateTimeRoundNearest,
   DATEADD( minute, ( DATEDIFF( minute, 0, DATEADD( minute,   15           , dateTimeX ) ) / 15 ) * 15, 0 ) AS dateTimeRoundUp

FROM CTE_dateTimes