×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Oracle SQL rounding method

Oracle SQL rounding method

Oracle SQL rounding method

(OP)
How do I get Oracle SQL to use the bankers rounding method instead of the symmetric arithmetic rounding method? Or has anyone written a function to do this in Oracle? MS Visual Basic defaults to this method but Oracle does not.

RE: Oracle SQL rounding method

HNH,

I'm not certain which method you mean by "Bankers' Rounding Method", but Oracle can handle them all:

CODE

SQL> select round(1234.56789,2) from dual;

ROUND(1234.56789,2)
-------------------
            1234.57

SQL> select floor(1234.56789) from dual;

FLOOR(1234.56789)
-----------------
             1234
SQL> select ceil(1234.56789) from dual;

CEIL(1234.56789)
----------------
            1235
SQL> select trunc(1234.56789,2) from dual;

TRUNC(1234.56789,2)
-------------------
            1234.56
Let us know which one you meant. <grin>

santaMufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
"Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty."

RE: Oracle SQL rounding method

(OP)
I am using an Oracle 8i database and from what I understand, the round function defaults to using the symmetric arithmetric rounding (or round-half-up) method. I am trying to to use the round-to-even method (or unbiased rounding, convergent rounding, statistician's rounding, dutch rounding, gaussian rounding, or banker's rounding).

Example (select round(45.4500,1) from dual) ==> 45.5

I am looking for 45.4 by round-to-even method.

RE: Oracle SQL rounding method

HNH,

If you can explain, step-by-step, the algorithm you want, you/we can build a function to apply that algorithm. If you post it, we will code. <grin>

santaMufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
"Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty."

RE: Oracle SQL rounding method

Thanks, Jim. Given your direction on the full explanation, here is code that HNH could use:

Section 1: User-defined function, "BANKERS_ROUND":

CODE

create or replace function bankers_round (val number, rnd_digit number) return number is
begin
    if val = trunc(val,rnd_digit+1) and mod(substr(trunc(val,rnd_digit),-1),2) = 0 then
        return trunc(val,rnd_digit);
    else
        return round(val,rnd_digit);
    end if;
end;
/

Function created.
Section 2 -- Sample invocations:

CODE

select bankers_round(123.4550001,2) from dual;

BANKERS_ROUND(123.4550001,2)
----------------------------
                      123.46

select bankers_round(123.4450001,2) from dual;

BANKERS_ROUND(123.4450001,2)
----------------------------
                      123.45

select bankers_round(123.445,2) from dual;

BANKERS_ROUND(123.445,2)
------------------------
                  123.44

select bankers_round(123.455,2) from dual;

BANKERS_ROUND(123.455,2)
------------------------
                  123.46

select bankers_round(123.5,0) from dual;

BANKERS_ROUND(123.5,0)
----------------------
                   124

select bankers_round(124.5,0) from dual;

BANKERS_ROUND(124.5,0)
----------------------
                   124
Let me know if this is what you needed.

santaMufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
"Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty."

RE: Oracle SQL rounding method

Nice function, Santa. Just a few tweaks to make it work exactly the same way the ROUND() function does...

CODE

create or replace function bankers_round (val number, rnd_digit number := 0) return number is
    v_rnd_digit  number;
    v_remainder  number;
begin
    v_rnd_digit := trunc(rnd_digit);

    v_remainder := (val - trunc(val,v_rnd_digit)) * power(10,v_rnd_digit + 1);

    if v_remainder < 5
    or (v_remainder = 5 and mod(trunc(val * power(10,v_rnd_digit)),2) = 0) then
        return trunc(val,v_rnd_digit);
    else
        return round(val,v_rnd_digit);
    end if;
end;
/
This version copes with cases where the decimal places parameter is missing, non-integer or negative:

CODE

SQL> select bankers_round(14.5) from dual

BANKERS_ROUND(14.5)
-------------------
                 14


SQL> select bankers_round(14.51) from dual

BANKERS_ROUND(14.51)
--------------------
                  15


SQL> select bankers_round(13.5) from dual

BANKERS_ROUND(13.5)
-------------------
                 14


SQL> select bankers_round(14.45, 1.5) from dual

BANKERS_ROUND(14.45,1.5)
------------------------
                    14.4


SQL> select bankers_round(145, -1) from dual

BANKERS_ROUND(145,-1)
---------------------
                  140


SQL> select bankers_round(145.1, -1) from dual

BANKERS_ROUND(145.1,-1)
-----------------------
                    150


SQL> select bankers_round(135, -1) from dual

BANKERS_ROUND(135,-1)
---------------------
                  140

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

RE: Oracle SQL rounding method

I was just working on a similar function (for Microsoft SQL Server) and a google search led me here.  I think there is a slight problem with the function posted by Chris Hunt.  It appears to be working correctly for positive numbers, but not for negative numbers.  I think this is simple enough to correct by modifying the code slightly.

Now, please understand that I know nothing about Oracle, so the code I post may not be correct.  If it's not, then I encourage someone to fix it for me.

CODE

create or replace function bankers_round (val number, rnd_digit number := 0) return number is
    v_rnd_digit  number;
    v_remainder  number;
begin
    v_rnd_digit := trunc(rnd_digit);

    v_remainder := (val - trunc(val,v_rnd_digit)) * power(10,v_rnd_digit + 1);

    if Abs(v_remainder) < 5
    or (Abs(v_remainder) = 5 and mod(trunc(val * power(10,v_rnd_digit)),2) = 0) then
        return trunc(val,v_rnd_digit);
    else
        return round(val,v_rnd_digit);
    end if;
end;
/

If I had access to Oracle, I would have tested this myself, but since I don't, I'll need to rely on you guys to test it for me.

If I'm not mistaken...

BankersRound(-123.45, 1) ==> -123.4
BankersRound(-123.55, 1) ==> -123.6
BankersRound(-123.65, 1) ==> -123.6
BankersRound(-123.75, 1) ==> -123.8

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: Oracle SQL rounding method

For the record, the following queries confirm that gmmastros's Bankers_Round function does, indeed, return the results he was expecting.

CODE

SQL> create or replace function bankers_round (val number, rnd_digit number := 0) return number is
  2      v_rnd_digit  number;
  3      v_remainder  number;
  4  begin
  5      v_rnd_digit := trunc(rnd_digit);
  6  
  7      v_remainder := (val - trunc(val,v_rnd_digit)) * power(10,v_rnd_digit + 1);
  8  
  9      if Abs(v_remainder) < 5
 10      or (Abs(v_remainder) = 5 and mod(trunc(val * power(10,v_rnd_digit)),2) = 0) then
 11          return trunc(val,v_rnd_digit);
 12      else
 13          return round(val,v_rnd_digit);
 14      end if;
 15  end;
 16  /

Function created.

SQL> select Bankers_Round(-123.45, 1) from dual;

BANKERS_ROUND(-123.45,1)
------------------------
                  -123.4

SQL> select Bankers_Round(-123.55, 1) from dual;

BANKERS_ROUND(-123.55,1)
------------------------
                  -123.6

SQL> select Bankers_Round(-123.65, 1) from dual;

BANKERS_ROUND(-123.65,1)
------------------------
                  -123.6

SQL> select Bankers_Round(-123.75, 1) from dual;

BANKERS_ROUND(-123.75,1)
------------------------
                  -123.8

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close