CODEWITHSUNDEEP
sql-serverdatetimerounding
bobialen
bobialen

Reputation: 109

Rounding Datetime to nearest 15 minutes in SQL Server

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:

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

select round(rec_datetime.......

Upvotes: 5

Views: 14963

Answers (4)

Eric
Eric

Reputation: 81

Nearest 15 minutes:

SELECT DateTimeX =  GETDATE(), RoundedDateTimeX = CAST(CAST(CAST(GETDATE() as float) * (24 * 60/15) + 0.5 as bigint) / (24.0 * 60/15) as datetime)

Nearest 15 minutes rounded up:

SELECT DateTimeX =  GETDATE(), RoundedDateTimeX = CAST(CAST(CAST(GETDATE() as float) * (24 * 60/15) + 1 as bigint) / (24.0 * 60/15) as datetime)

Note: The decimal 24.0 in devisor changes type back to float.

Upvotes: 0

Kevin Swann
Kevin Swann

Reputation: 1038

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

Upvotes: 21

Raushan Kuamr Jha
Raushan Kuamr Jha

Reputation: 463

In this case you need to round the minute to nearest 15

try query like this:

SELECT

 original_datetime

 --: this will give you the minute part
 , datepart(minute, original_datetime) AS minuteFromDate

 --: now get the nearest minute part to 15min, round it nearest to 15
 , round(datepart(minute, original_datetime)  * 1.0 / 15 , 0) * 15.0 AS roundedToNearest15

 -- now remove the minute from original datetime, & add the rounded minute to the resultant datetime value
 -- this will give you expected result
 , dateadd(minute 
             , (round(datepart(minute, original_datetime) * 1.0 / 15 , 0) * 15.0)
             , dateadd(minute, -datepart(minute, original_datetime), original_datetime)
           ) as rec_datetime

FROM (values('2015-11-24 19:06:00.000')
           ,('2015-11-24 19:09:00.000')
         ,('2015-11-24 19:56:00.000')
         ,('2015-11-24 19:48:00.000')
     ) x(original_datetime)

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44326

Try something like this:

SELECT
  dateadd(minute, datediff(minute, '1999-12-31 23:52:30', col) / 15*15, '2000-01-01')
FROM (values('2015-11-24 19:06:00.000'),('2015-11-24 19:09:00.000')) x(col)

Result:

2015-11-24 19:00:00.000
2015-11-24 19:15:00.000

Upvotes: 2

Related Questions