number rounding to nearest even number with given precision in oracle

Question

I am trying to round a number in oracle the same way as python does: to nearest even number (to a given precision):

python:
round(0.3125) -> 0.312

Oracle:
select round(0.3125, 3) from dual -> 0.313

Oracle can do this by specifying the input as float when rounding to integers:

select round(2.5f) from dual -> 2

select round(2.5)  from dual -> 3

But:

select round(0.3125f, 3) from dual -> 0.313 and not 0.312.
Any ideas?

Answer 1

I am trying to round a number in oracle the same way as python does: to nearest even number (to a given precision):

Taken from my answer here you can implement round half even as:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE test_data( value ) AS
SELECT (LEVEL -11)/20 FROM DUAL CONNECT BY LEVEL <= 21
/

CREATE FUNCTION round_half_even(
  value NUMBER,
  prec  INTEGER DEFAULT 0
) RETURN NUMBER
IS
  whole NUMBER := POWER( 10, -prec );
BEGIN
  RETURN CASE
         WHEN ABS( MOD( value, 2*whole ) ) = 0.5*whole
         THEN TRUNC( value, prec )
         ELSE ROUND( value, prec )
         END;
END;
/

Query 1:

SELECT value,
       ROUND( value , 1),
       round_half_even( value, 1 )
FROM   test_data

Results:

| VALUE | ROUND(VALUE,1) | ROUND_HALF_EVEN(VALUE,1) |
|-------|----------------|--------------------------|
|  -0.5 |           -0.5 |                     -0.5 |
| -0.45 |           -0.5 |                     -0.4 |
|  -0.4 |           -0.4 |                     -0.4 |
| -0.35 |           -0.4 |                     -0.4 |
|  -0.3 |           -0.3 |                     -0.3 |
| -0.25 |           -0.3 |                     -0.2 |
|  -0.2 |           -0.2 |                     -0.2 |
| -0.15 |           -0.2 |                     -0.2 |
|  -0.1 |           -0.1 |                     -0.1 |
| -0.05 |           -0.1 |                        0 |
|     0 |              0 |                        0 |
|  0.05 |            0.1 |                        0 |
|   0.1 |            0.1 |                      0.1 |
|  0.15 |            0.2 |                      0.2 |
|   0.2 |            0.2 |                      0.2 |
|  0.25 |            0.3 |                      0.2 |
|   0.3 |            0.3 |                      0.3 |
|  0.35 |            0.4 |                      0.4 |
|   0.4 |            0.4 |                      0.4 |
|  0.45 |            0.5 |                      0.4 |
|   0.5 |            0.5 |                      0.5 |

However

The Python documentation states:

The documentation for the built-in round() function says that it rounds to the nearest value, rounding ties away from zero. Since the decimal fraction 2.675 is exactly halfway between 2.67 and 2.68, you might expect the result here to be (a binary approximation to) 2.68. It’s not, because when the decimal string 2.675 is converted to a binary floating-point number, it’s again replaced with a binary approximation, whose exact value is

2.67499999999999982236431605997495353221893310546875

Since this approximation is slightly closer to 2.67 than to 2.68, it’s rounded down.

So the way to implement python's ROUND() function in Oracle is to just use the built-in ROUND() function and know that python's implementation has slight rounding errors.

Answer 2

Oracle supports ROUND_TIES_TO_EVEN (number):

SELECT ROUND(0.3125, 3)              AS ROUND       -- 0.313
      ,ROUND_TIES_TO_EVEN(0.3125, 3) AS ROUND_EVEN  -- 0.312
FROM dual;

db<>fiddle demo

Answer 3

try this:

DECODE ( SIGN(MOD((0.3127*10000),10)- 5),1,round(0.3125,3),round( floor(0.3125*1000)/1000,3) )