Round to nearest 15 minute interval

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

Answer

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]);