Round Half to Even in Oracle

Oracle standard ROUND function is rounding away from 0.
In order to round half to even (aka bankers’ rounding) in Oracle, you have to create a custom function.

Basic idea

Consider the case of rounding to an integer.

• To judge whether the number is half-way between two integers, you have to divide the absolute value by 1 and see the remainder.
  If the remainder is 0.5, you need to round to even. Otherwise, you can use the standard ROUND function
• Actually, you had better divide the absolute value by 2 rather than 1 in order to round half to even.
  In this case, you need to round to even when the remainder is 0.5 or 1.5.
  When the remainder is 0.5, you have to round towards 0 (i.e. truncate).
  When the remainder is 1.5, you have to round away from 0. (You can use the standard ROUND function.)

Function

These ideas can be summarized as the following function.

CREATE OR REPLACE FUNCTION ROUND_HALF_EVEN(
    n       NUMBER,
    integer NUMBER DEFAULT 0
) RETURN NUMBER
IS
BEGIN
    IF MOD(ABS(n) * POWER(10, integer), 2) = 0.5 THEN
        RETURN TRUNC(n, integer);
    ELSE
        RETURN ROUND(n, integer);
    END IF;
END;

---

From Oracle Database 18c, you can use ROUND_TIES_TO_EVEN function.

ROUND_TIES_TO_EVEN (number)

docs.oracle.com

4