Formula to use to convert the hours entered to 1/4th hour

I am creating a Oracle fast formula where I am trying to round the time entered by employee to the nearest 1/4 hour.

15-22  minutes = 0.25 hours
23- 37 minutes = 0.50 hours
38-52 minutes = 0.75 hours
53 to 1 hr 7 mins  = 1 hour

I am able to get the hours entered.

For Eg - Time entered - 5 PM - 8.25 PM will be 3.42 hours entered Now this should be converted to the nearest 1/4 hour - 3.75.

what calculation formula should I use to convert this to 3.42 hours ?

Solution

You can TRUNCate the time to the start of the hour and then round the minutes and seconds part to the nearest 15 minutes and add:

SELECT time,
       TRUNC(time, 'HH') + ROUND((time - TRUNC(time, 'HH'))*4*24)/4/24 AS rounded_time
FROM   times

Which, for the sample data:

CREATE TABLE times (time) AS
SELECT TRUNC(SYSDATE, 'HH') + NUMTODSINTERVAL(LEVEL, 'MINUTE')
FROM   DUAL
CONNECT BY LEVEL <= 60;

Outputs: