Precision nightmare in Java and MSSQL

Question

Hi everyone,

I've been struggling with precision nightmare in Java and MSSQL up to the point when I don't know anymore. Personally, I understand the issue and the underlying reason for it, but explaining that to the client half way across the globe is something unfeasible (at least for me).

The situation is this. I have two columns in MSSQL - Qty INT and Price FLOAT. The values for these are - 1250 and 10.8601 - so in order to get the total value its Qty * Price and result is 13575.124999999998 (in both Java and MSSQL). That's correct. The issue is this - the client doesn't want to see that, they see that number only as 13575.125 and that's it. On one place they way to see it in 2 decimal precision and another in 4 decimals. When displaying in 4 decimals the number is correct - 13575.125, but when displaying in 2 decimals they believe it is wrong - 13575.12 - should instead be 13575.13!

Help.

Answer 1

Use BigDecimal. Float is not an approciate type to represent money. It will handle the rounding properly. Float will always produce rounding errors.

Answer 2

Don't use the float datatype for price. You should use "Money" or "SmallMoney".

Here's a reference for [MS SQL DataTypes][1].

[1]: http://webcoder.info/reference/MSSQLDataTypes.html

Correction: Use Decimal(19,4)

Thanks Yishai.

Answer 3

For storing monetary amounts floating point values are not the way to go. From your description I would probably handle amounts as long integers with as value the monetary amount multiplied by 10^5 as database storage format.

You need to be able to handle calculations with amounts that do not loose precision, so here again floating point is not the way to go. If the total sums between debit and credit are off by 1 cent in a ledger, the ledger fails in the eyes of financial people, so make sure your software operates in their problem domain, not yours. If you can not use existing classes for monetary amounts, you need to build your own class that works with amount * 10^5 and formats according to the precision wanted only for input and output purposes.

Answer 4

Your problem is that you are using floats. On the java side, you need to use BigDecimal, not float or double, and on the SQL side you need to use Decimal(19,4) (or Decimal(19,3) if it helps jump to your precision level). Do not use the Money type because math on the Money type in SQL causes truncation, not rounding. The fact that the data is stored as a float type (which you say is unchangeable) doesn't affect this, you just have to convert it at first opportunity before doing math on it.

In the specific example you give, you need to first get the 4 decimal precision number and put it in a BigDecimal or Decimal(19,4) as the case may be, and then further round it to 2 decimal precision. Then (if you are rounding up) you will get the result you want.

Answer 5

If you can't fix the underlying database you can fix the java like this:

import java.text.DecimalFormat;

public class Temp {

    public static void main(String[] args) {
        double d = 13575.124999999;
        DecimalFormat df2 = new DecimalFormat("#.##");
        System.out.println( " 2dp: "+ Double.valueOf(df2.format(d)) );

        DecimalFormat df4 = new DecimalFormat("#.####");
        System.out.println( " 4dp: "+Double.valueOf(df4.format(d)) );
    }
}

Answer 6

Although you shouldn't be storing the price as a float in the first place, you can consider converting it to decimal(38, 4), say, or money (note that money has some issues since results of expressions involving it do not have their scale adjusted dynamically), and exposing that in a view on the way out of SQL Server:

SELECT Qty * CONVERT(decimal(38, 4), Price)

Answer 7

So, given that you can't change the database structure (which would probably be the best option, given that you are using a non-fixed-precision to represent something that should be fixed/precise, as many others have already discussed), hopefully you can change the code somewhere. On the Java side, I think something like @andy_boot answered with would work. On the SQL side, you basically would need to cast the non-precise value to the highest precision you need and continue to cast down from there, basically something like this in the SQL code:

declare @f float,
  @n numeric(20,4),
  @m money;

select @f = 13575.124999999998,
  @n = 13575.124999999998,
  @m = 13575.124999999998

select @f, @n, @m
select cast(@f as numeric(20,4)), cast(cast(@f as numeric(20,4)) as numeric(20,2))
select cast(@f as money), cast(cast(@f as money) as numeric(20,2))

Answer 8

I think I see the problem.

10.8601 cannot be represented perfectly, and so while the rounding to 13575.125 works OK it's difficult to get it to round to .13 because adding 0.005 just doesn't quite get there. And to make matters worse, 0.005 doesn't have an exact representation either, so you end up just slightly short of 0.13.

Your choices are then to either round twice, once to three digits and then once to 2, or do a better calculation to start with. Using long or a high precision format, scale by 1000 to get *.125 to *125. Do the rounding using precise integers.

By the way, it's not entirely correct to say one of the endlessly repeated variations on "floating point is inaccurate" or that it always produces errors. The problem is that the format can only represent fractions that you can sum negative powers of two to create. So, of the sequence 0.01 to 0.99, only .25, .50, and .75 have exact representations. Consequently, FP is best used, ironically, by scaling it so that only integer values are used, then it is as accurate as integer datatype arithmetic. Of course, then you might as well have just used fixed point integers to start with.

Be careful, scaling, say, 0.37 to 37 still isn't exact unless rounded. Floating point can be used for monetary values but it's more work than it is worth and typically the necessary expertise isn't available.

Answer 9

You can also do a DecimalFormat and then round using it.

DecimalFormat df = new DecimalFormat("0.00"); //or "0.0000" for 4 digits.
df.setRoundingMode(RoundingMode.HALF_UP);
String displayAmt = df.format((new Float(<your value here>)).doubleValue());

And I agree with others that you should not be using Float as a DB field type to store currency.

Answer 10

If you can't change the database to a fixed decimal datatype, something you might try is rounding by taking truncate((x+.0055)*10000)/10000. Then 1.124999 would "round" to 1.13 and give consistent results. Mathematically this is unreliable, but I think it would work in your case.