Oracle ROUND_TIES_TO_EVEN Function

ROUND rounds a value that is exactly halfway, such as 2.5, away from zero, to 3. Done over thousands of amounts, always rounding halves up adds a small upward bias to every total. ROUND_TIES_TO_EVEN rounds halfway values to the nearest even number instead, so 2.5 becomes 2 and 3.5 becomes 4, and the ups and downs cancel out. This is banker's rounding, the method of the IEEE 754 standard.

Code for This Guide

The main example is in the examples/numeric-functions folder of the Oracle Database 26ai code repository on GitHub, with its output. The repository also has the NIMBUS sample schema in the setup/nimbus folder.

It comes from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

Syntax:

round_ties_to_even(n [, integer])

n is the number to round. The optional integer is the number of decimal places, as with ROUND: 2 rounds to hundredths, 0 (the default) to a whole number, and -1 to tens. Values that are not exactly halfway are rounded to the nearest value, the same as ROUND.

ROUND Compared with ROUND_TIES_TO_EVEN

Example:

select n, round(n) as round, round_ties_to_even(n) as ties_to_even,
       round_ties_to_even(n * 10, -1) / 10 as to_tens
from   (values (0.5), (1.5), (2.5), (3.5), (-2.5)) t (n);

Output:

      N    ROUND    TIES_TO_EVEN    TO_TENS
_______ ________ _______________ __________
    0.5        1               0          0
    1.5        2               2          2
    2.5        3               2          2
    3.5        4               4          4
   -2.5       -3              -2         -2

ROUND sends every half away from zero: 0.5 to 1, 2.5 to 3, -2.5 to -3. ROUND_TIES_TO_EVEN sends each half to the even neighbor: 0.5 to 0, 1.5 and 2.5 to 2, 3.5 to 4. The to_tens column rounds n * 10 to tens with -1 and divides back, giving the same result at another scale.

Why It Matters for Totals

Rounding the ten values 0.5, 1.5, ..., 9.5 shows the drift. Their exact sum is 50.

Example:

-- rounding ten halves: ROUND drifts upward, ROUND_TIES_TO_EVEN does not
select sum(n) as exact_sum, sum(round(n)) as round_sum, sum(round_ties_to_even(n)) as even_sum
from   (select level - 0.5 as n from dual connect by level <= 10);

select round(2.345, 2) as round_2dp, round_ties_to_even(2.345, 2) as even_2dp,
       round_ties_to_even(2.355, 2) as even_2dp_b, round_ties_to_even(2.3451, 2) as not_a_tie
from   dual;

Output:

   EXACT_SUM    ROUND_SUM    EVEN_SUM
____________ ____________ ___________
          50           55          50

   ROUND_2DP    EVEN_2DP    EVEN_2DP_B    NOT_A_TIE
____________ ___________ _____________ ____________
        2.35        2.34          2.36         2.35

ROUND adds half a unit for every value, ending at 55; ROUND_TIES_TO_EVEN rounds half of them down and half up, keeping the sum at 50. The second query shows decimal places: 2.345 goes to 2.34 and 2.355 to 2.36, while 2.3451 is not a tie and rounds to 2.35 like ROUND.

Things to Know

• Only values exactly halfway are affected, so on most data the two functions agree.
• NUMBER stores decimal values exactly, so 2.345 really is halfway. BINARY_DOUBLE values may not be exactly halfway, since many decimals have no exact binary form.
• Use it where totals of many rounded amounts must not drift, such as interest, allocations, or statistics; check whether your accounting rules require it.

Conclusion

ROUND_TIES_TO_EVEN rounds like ROUND except that exact halves go to the nearest even number. That removes the upward bias of rounding halves away from zero, so totals of many rounded values stay close to the totals of the exact ones.