Venkatesan Ramalingam

Greenhorn

Posts: 6

posted 10 years ago

Why Java and Oracle have a different approach in rounding the numbers with decimals?

In Java,

Math.round(1.5) gives the output as 2

Math.round(-1.5) gives the output as -1

In Oracle,

select round(1.5) from dual gives the output as 2

select round(-1.5) from dual gives the output as 2

If we look the outputs in both the platforms, positive rounding works fine as expected. But not the negative rounding.

This leads to produce mismatch reports which dealing with numbers and calculations.

In Java,

Math.round(1.5) gives the output as 2

Math.round(-1.5) gives the output as -1

In Oracle,

select round(1.5) from dual gives the output as 2

select round(-1.5) from dual gives the output as 2

If we look the outputs in both the platforms, positive rounding works fine as expected. But not the negative rounding.

This leads to produce mismatch reports which dealing with numbers and calculations.

Ilja Preuss

author

Sheriff

Sheriff

Posts: 14112

posted 10 years ago

Well, probably because SQL and Java were specified by different people. There is no generally accepted procedure for rounding in mathematics...

The soul is dyed the color of its thoughts. Think only on those things that are in line with your principles and can bear the light of day. The content of your character is your choice. Day by day, what you do is who you become. Your integrity is your destiny - it is the light that guides your way. - Heraclitus

Campbell Ritchie

Sheriff

Posts: 53742

127

posted 10 years ago

Don't know. I googled for rounding, and found a couple of discussions. Here, and here. There doesn't seem to be a consistent definition of rounding.

But at least round 2.5->3 and 3.5->4 is consistent. In which case, you round up on halves. So -1.5 rounds UP to -1. Yes, -1 is more than -1.5. Which is similar to what it says in the API specification for java.lang.Math.

CR

But at least round 2.5->3 and 3.5->4 is consistent. In which case, you round up on halves. So -1.5 rounds UP to -1. Yes, -1 is more than -1.5. Which is similar to what it says in the API specification for java.lang.Math.

CR

Ilja Preuss

author

Sheriff

Sheriff

Posts: 14112

posted 10 years ago
The soul is dyed the color of its thoughts. Think only on those things that are in line with your principles and can bear the light of day. The content of your character is your choice. Day by day, what you do is who you become. Your integrity is your destiny - it is the light that guides your way. - Heraclitus

Perhaps Oracle/SQL uses the odd/even rule: http://ts.nist.gov/ts/htdocs/230/235/vtmpresent/5pretest/tsld027.htm

Peter Chase

Ranch Hand

Posts: 1970

posted 10 years ago

In general, I would think that if you care about the exact results of functions, with exact parameters, then you should not be using floating-point (float, double).

Any floating-point number, represented in a computer, is potentially inexact. Some numbers can be represented exactly, but it depends upon the precise details of the floating-point representation used. You should try to avoid making your code depend on that.

If you care about exact numbers, use integer arithmetic (maybe with implicit scaling, for fixed decimal places) or maybe BigDecimal.

This is particularly crucial for financial calculations. With the possible exception of some complicated derivatives, on which I am unqualified to comment, financial calculations should never be done in floating point.

Any floating-point number, represented in a computer, is potentially inexact. Some numbers can be represented exactly, but it depends upon the precise details of the floating-point representation used. You should try to avoid making your code depend on that.

If you care about exact numbers, use integer arithmetic (maybe with implicit scaling, for fixed decimal places) or maybe BigDecimal.

This is particularly crucial for financial calculations. With the possible exception of some complicated derivatives, on which I am unqualified to comment, financial calculations should never be done in floating point.

Betty Rubble? Well, I would go with Betty... but I'd be thinking of Wilma.