Oracle ceil for decimal numbers

Question

When rounding up to 2 decimal places, the value 4.01132141 would be rounded to 4.02 because it exceeds 4.01.

How can you do this in PL/SQL?

Solution 1

One way would be to do ceil(value*100)/100, but that seems inelegant. Not sure there's any way to make round behave the way you want.

Solution 2

The function to 'round up' is CEIL, but it generates an integer.

The function to 'round down' is FLOOR, but it too generates an integer.

The function to 'round nearest' is ROUND, and it allows you to specify a number of decimal places (dp).

Note that CEIL rounds to an integer; to round to 2 dp, you'd have to multiply by 100, use CEIL, and divide by 100.

---

To get the answer reasonably directly, use:

ROUND(value+0.005, 2)

This works because, for the example data of 4.01132141, the value passed to ROUND is 4.01632, and when rounded to 2 dp, that becomes 4.02. If the value started as 4.0593, say, then the value passed to ROUND would be 4.0643, which when rounded to 2 dp becomes 4.06, as required.

There are a couple of tricky bits there:

1. If the number of dp varies, the value to be added (0.005 in the example) varies. You could create a table to hold the number of decimal places in one column and the rounding value to add in the other. Alternatively, you could use an expression with powers of 10, etc.
2. Deciding on the correct behaviour for negative numbers. Does -4.01132141 become -4.02 or -4.01? You might need to play with SIGN and ABS functions to get that to work as you want.

Solution 3

I faced the same issue and came up with the following statement, it has worked fine so far.

select 4.01132141+(mod((ceil(4.01132141)-4.01132141)*1000,10)/1000) from dual

Comments

[Mark Byers]

What should "0" be rounded to?

[Jonathan Leffler]

0 because it doesn't exceed 0.00

[Adam Musch]

Can't repeat: exec dbms_output.put_line( round (4.01132141, 2) ); on 10gR2 returns 4.01.

[Rick Smith]

Welcome to Stack Overflow! Please edit with more information. Code-only and "try this" answers are discouraged, because they contain no searchable content, and don't explain why someone should "try this".