CODEWITHSUNDEEP
sqlt-sql
daniel charlesworth
daniel charlesworth

Reputation: 23

T-SQL: Rounding Time in to 15 minutes but only after 5 minutes past the 15min

I have a table with a datetime field. I'm trying to round up/down the time in 15 minute intervals. But with non standard mathematical rounding rules where rounding up happens if its greater than 5 minutes past the 15 minute interval.

For example

IF 06:05 round down to 06:00

IF 06:06 round up to 06:15

IF 06:20 round down to 06:15

IF 06:21 round up to 06:30 and so on..

I've managed to find here T-SQL: Round to nearest 15 minute interval to round the nearest 15 minutes but this uses mathematical rounding meaning 06:07 would still round down to 06:00 instead of rounding up to 06:15.

Below code where i've got to:

cast(datepart(hour, getdate()) + datepart(minute, getdate()) / 60.00 as decimal(5, 2))

Upvotes: 0

Views: 236

Answers (1)

Eric Brandt
Eric Brandt

Reputation: 8101

Just use a couple of date tricks.

This code will give you the top of the hour for the time you're evaluating (minutes effectively removed by adding up the hours since the 0 date in SQL):

select dateadd(hour, datediff(hour, 0, getdate()), 0)

From there, you need a CASE expression to evaluate which quartile of the hour the time in question falls into (just a snippet here):

  case 
    when datepart(minute, dtm) > 50 then 60
    when datepart(minute, dtm) > 35 then 45
    when datepart(minute, dtm) > 20 then 30
    when datepart(minute, dtm) > 5  then 15
    else 0
  end

Put those two pieces together with a DATEADD to decide how many minutes we're adding to that even hour mark:

declare @dtms table (dtm datetime);
insert @dtms (dtm)
values ('2019-07-16T12:05:00'),
       ('2019-07-16T12:06:00'),
       ('2019-07-16T12:21:00'),
       ('2019-07-16T12:29:00'),
       ('2019-07-16T12:35:00'),
       ('2019-07-16T12:38:00'),
       ('2019-07-16T12:56:00')

select
  dtm,
  dateadd(minute, 
    case 
      when datepart(minute, dtm) > 50 then 60
      when datepart(minute, dtm) > 35 then 45
      when datepart(minute, dtm) > 20 then 30
      when datepart(minute, dtm) > 5  then 15
      else 0
    end, dateadd(hour, datediff(hour, 0, dtm), 0)) as rounded
from @dtms;

Results:

+-------------------------+-------------------------+
|           dtm           |         rounded         |
+-------------------------+-------------------------+
| 2019-07-16 12:05:00.000 | 2019-07-16 12:00:00.000 |
| 2019-07-16 12:06:00.000 | 2019-07-16 12:15:00.000 |
| 2019-07-16 12:21:00.000 | 2019-07-16 12:30:00.000 |
| 2019-07-16 12:29:00.000 | 2019-07-16 12:30:00.000 |
| 2019-07-16 12:35:00.000 | 2019-07-16 12:30:00.000 |
| 2019-07-16 12:38:00.000 | 2019-07-16 12:45:00.000 |
| 2019-07-16 12:56:00.000 | 2019-07-16 13:00:00.000 |
+-------------------------+-------------------------+

Upvotes: 3

Related Questions