Oracle ROUND HALF EVEN

Question

Is there any Oracle function to perform the ROUND HALF EVEN?

I've found this post MySQL round half however I couldn't make it work in Oracle

CREATE FUNCTION roundHalfEven (numberToRound IN NUMBER, roundPrecision IN NUMBER) 
   RETURN NUMBER 
   IS roundedNumber NUMBER;
   BEGIN 
      DECLARE digitEvenOdd NUMBER;
      DECLARE digitPosition NUMBER;
      DECLARE digitToRound NUMBER;
      DECLARE roundedNumber DECIMAL(20,6) DEFAULT 0;

      SET digitPosition = INSTR(numberToRound, '.');

      IF (roundingPrecision < 1) THEN
        SET digitPosition = digitPosition + roundingPrecision;
      ELSE
        SET digitPosition = digitPosition + roundingPrecision + 1;
      END IF;

      IF (digitPosition > 0 AND digitPosition <= CHAR_LENGTH(numberToRound)) THEN
        SET digitToRound = CAST(SUBSTR(numberToRound, digitPosition, 1) AS UNSIGNED);
        SET digitPosition = digitPosition - 1;

        IF (digitPosition > 0 AND digitPosition <= CHAR_LENGTH(numberToRound)) THEN
            SET digitEvenOdd = CAST(SUBSTR(numberToRound, digitPosition, 1) AS UNSIGNED);
        END IF;
      END IF;

      IF (digitToRound > -1) THEN
        IF (digitToRound >= 5 AND digitEvenOdd IN (1,3,5,7,9)) THEN
          SET roundedNumber = ROUND(numberToRound, roundingPrecision);
        ELSE
          SET roundedNumber = TRUNCATE(numberToRound, roundingPrecision);
        END IF;
      ELSE IF (roundingPrecision > 0) THEN
        SET roundedNumber = numberToRound;
      END IF;

      RETURN(roundedNumber); 
    END;
/

Please **[EDIT]** your question and add some [sample data](http://plaintexttools.github.io/plain-text-table/) and the expected output based on that data. [**Formatted text**](http://stackoverflow.com/help/formatting) please, [**no screen shots**](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). **[edit]** your question - do **not** post code or additional information in comments. — , Oct 27 '17 at 10:26
Also add the code you tried that you "*couldn't make to work*" **[Edit]** your question - do **not** post code or additional information in comments. — , Oct 27 '17 at 10:27
https://stackoverflow.com/questions/1292616/oracle-bankers-rule/1292712#1292712 has a link to http://www.tek-tips.com/viewthread.cfm?qid=1487672 — , Oct 27 '17 at 10:51

MT0 · Accepted Answer · 2017-10-27T11:28:27.887

SQL Fiddle

Oracle 11g R2 Schema Setup:

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

Query 1:

SELECT value,
       ROUND( value ),
       CASE 
       WHEN MOD( ABS( value ), 2 ) = 0.5
       THEN TRUNC( value )
       ELSE ROUND( value )
       END AS round_half_even
FROM   test_data

Results:

| VALUE | ROUND(VALUE) | ROUND_HALF_EVEN |
|-------|--------------|-----------------|
|    -5 |           -5 |              -5 |
|  -4.5 |           -5 |              -4 |
|    -4 |           -4 |              -4 |
|  -3.5 |           -4 |              -4 |
|    -3 |           -3 |              -3 |
|  -2.5 |           -3 |              -2 |
|    -2 |           -2 |              -2 |
|  -1.5 |           -2 |              -2 |
|    -1 |           -1 |              -1 |
|  -0.5 |           -1 |               0 |
|     0 |            0 |               0 |
|   0.5 |            1 |               0 |
|     1 |            1 |               1 |
|   1.5 |            2 |               2 |
|     2 |            2 |               2 |
|   2.5 |            3 |               2 |
|     3 |            3 |               3 |
|   3.5 |            4 |               4 |
|     4 |            4 |               4 |
|   4.5 |            5 |               4 |
|     5 |            5 |               5 |

Or as a function:

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 |

Oracle ROUND HALF EVEN

1 Answers1

Linked