how to do roundup in oracle with 2 decimals as in

Question

hi i have requirement like round by 2 decimal if i use round function it get round but wont be round up if third decimal is less than 5. my requirement is what ever the 3rd decimal 2nd decimal should be round up. Is it possible?

eg: 17.813 need to be 17.82

20.126 need to be 20.13

Select round(17.813,2) from dual will give 17.81

how to get this?

Solution

You can multiply by 100, use the ceil() function to 'round up' (kind of) that adjusted value to the nearest integer, and then divide by 100 again:

ceil(<your number> * 100) / 100

Demo:

with t (n) as (
  select 17.813 from dual
  union all select 20.126 from dual
  union all select 1.000 from dual
  union all select 1.001 from dual
  union all select 1.005 from dual
  union all select 1.009 from dual
  union all select 1.010 from dual
)
select n, ceil(n * 100) / 100 as rounded_up
from t;

         N ROUNDED_UP
---------- ----------
    17.813      17.82
    20.126      20.13
         1          1
     1.001       1.01
     1.005       1.01
     1.009       1.01
      1.01       1.01

The round() function uses (for positive numbers):

ROUND(n, integer) = FLOOR(n * POWER(10, integer) + 0.5) * POWER(10, -integer)

So you can generalise a round-up version in a similar way as:

ceil(n * power(10, integer)) * power(10, -integer)

You need to look at how you want to deal with negative values though, though this may behave as you want already; plugging in 2 as the 'integer' value:

with t (n) as (
  select 17.813 from dual
  union all select 20.126 from dual
  union all select 1.000 from dual
  union all select 1.001 from dual
  union all select 1.005 from dual
  union all select 1.009 from dual
  union all select 1.010 from dual
  union all select -1.000 from dual
  union all select 0 from dual
  union all select -1.001 from dual
  union all select -1.005 from dual
  union all select -1.009 from dual
  union all select -1.010 from dual
)
select n, ceil(n * power(10, 2)) * power(10, -2) as rounded_up
from t;

         N ROUNDED_UP
---------- ----------
    17.813      17.82
    20.126      20.13
         1          1
     1.001       1.01
     1.005       1.01
     1.009       1.01
      1.01       1.01
        -1         -1
         0          0
    -1.001         -1
    -1.005         -1
    -1.009         -1
     -1.01      -1.01