# 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