Round to nearest 15 minute interval

Solution for Round to nearest 15 minute interval
is Given Below:

I’ve seen this question asked and answered for time that is stored as a date, but I have duration of hours and minutes stored as numeric(4,2). For example 2.12, is 2 hours 12 minutes. I need to round that to the nearest 15 minute interval, doing this (CONVERT([numeric](4,2),round(2.12/(25),(2))*(25))) doesn’t work because it’s base 10 and rounds time incorrectly. Any help would be much appreciated, thanks.

edit: the result I am looking for from the example would be 2.25.

For my use case the duration needs to be represented in quarters,

15 minutes = .25

30 minutes = .50

45 minutes = .75

Ideally you wouldn’t be storing your duration as a decimal – you should be stored as a time if always less than 24 hours or a datetime2 if multiple days are possible.

Convert to a time by converting to a string in acceptable time format. Then use your favourite solution as found here.

select
  -- Original Value
   D.[Value]
   -- As Time
   , T.[Time]
   -- As Time Rounded
   , RT.[Time]
   -- As decimal
   , convert(decimal(9,2),datepart(hour,RT.[Time]) + datepart(minute,RT.[Time]) / 60.0)
from (
  -- Test Values
  values (2.12), (2.02), (0.12)
) D ([Value])
cross apply (
  -- Convert to time datatype
  values (convert(time(0), convert(varchar(8),convert(int, D.[Value])) + ':' + substring(convert(varchar(8),D.[Value] - convert(int, D.[Value])),3,8)))
) T ([Time])
cross apply (
  -- Round using your favourite method
  values (convert(time(0), dateadd(minute, round(datediff(minute, 0, T.[Time]) / 15.0, 0) * 15, 0)))
) RT ([Time]);

Here is yet another option

declare @YourTable table (duration numeric(4,2))
Insert Into @YourTable values
 ( 2.12 )
,( 1.30 )
,( 1.55 )

Select  Duration 
       ,NewValue = floor(duration) + ceiling(((duration % 1) * 100 / 60 ) * 4) / 4
 From  @YourTable

Results

Duration    NewValue
2.12        2.250000
1.30        1.500000
1.55        2.000000

You can’t perform any computation on your duration as it is not in base 10 and converting it to base 10 may result in lost in precision (example 2 hour 1 minute in your format is 2.01, converting will be 2.01666666…)

best is to convert it to total minutes and then round it and convert it back to your format


declare @duration numeric(4,2) = 2.12;

with 
cte1 as
(
    select  tot_mins = (floor(@duration) * 60) 
                     + (@duration * 100) % 100
),
cte2 as
(
    select  *, tot_mins_rounded = round(tot_mins / 15.0, 0) * 15
    from    cte1
)
select  *,
        convert_back_to_your_format = floor(tot_mins_rounded / 60) 
                                    + (tot_mins_rounded % 60) / 100.0
from    cte2

Result :

tot_minstot_mins_roundedconvert_back_to_your_format
132.00135.0000002.15000000000