• Post author:
  • Post category:SQL
  • Reading time:2 mins read
Suppose you have a requirement to round any decimal numeric value to nearest 0.25 in Oracle SQL, then you have to find some other way to round because in Oracle there is no such parameters in Round or Trunc function to round a value to nearest 0.25.
In my case, I was dealing with time data in 100 format mean 15 minutest is equal to 0.25 and there was a need to round that time to nearest 15 minutes, examples are given below:

0.01 to 0.25 = 0.25  (15 minutes)
0.26 to 0.50 = 0.50  (30 minutes)
0.51 to 0.75 = 0.75  (45 minutes)
0.76 to 1       = 1       (1 hours) 
You can complete this task by using following formula in SQL:

Select CEIL (0.01 / 0.25) * 0.25 Nearest_25 from dual;

NEAREST_25
----------
       .25

Select CEIL (0.26 / 0.25) * 0.25 Nearest_25 from dual;

NEAREST_25
----------
        .5

Select CEIL (0.51 / 0.25) * 0.25 Nearest_25 from dual;

NEAREST_25
----------
       .75

Select CEIL (0.76 / 0.25) * 0.25 Nearest_25 from dual;

NEAREST_25
----------
         1

Vinish Kapoor

An Oracle Apex Consultant, Oracle ACE, and founder of foxinfotech.in and orclqa.com a question and answer forum for developers.