Learning is not a spectator sport

Ever thought something should be pretty simple, but turns out it isn’t?

A perfect example of this is the topic of todays post… Rounding!

A quick look at the Wiki for Rounding discusses (I kid you not) THIRTEEN 🤣 methods of rounding, and that doesn’t include some of the other sub-topics on rounding with different degrees of precision etc

• Rounding up
• Rounding down
• Rounding half up
• Rounding half to even
• Rounding away from zero
• Rounding toward zero
• Rounding half away from zero
• Rounding half down
• Rounding half to odd
• Rounding half toward zero
• Alternating tie-breaking
• Random tie-breaking
• Stochastic rounding

You probably know some of the methods which have a direct implementation in Oracle

Rounding up

Use the CEIL function

Rounding down

Use the FLOOR function

Rounding half up

Use the ROUND function

Rounding half to even

Did you know we have a native implementation of “Rounding Half To Even”

SQL> select round_ties_to_even(3.5);

ROUND_TIES_TO_EVEN(3.5)
-----------------------
                      4

SQL> select round_ties_to_even(2.5);

ROUND_TIES_TO_EVEN(2.5)
-----------------------
                      2

Rounding toward zero

Use the TRUNC function

For others prefixed with “Rounding…” above, they can be done with simple expressions around existing functions, for example:

Rounding away from zero

case when X <= 0 then FLOOR(X) else CEIL(X) end

For the alternating, random tie breaking and stochastic you’re looking at some more work, which is (because it’s Christmas) left as a reader exercise 🤣

Ho Ho Ho! Merry Christmas!