An exact nightmare in Java and SQL Server

Question

I am struggling with the exact nightmare in Java and SQL Server to the point where I no longer know. Personally, I understand the problem and the main reason for this, but explaining that the client halfway around the world is something impossible (at least for me).

The situation is as follows. I have two columns in SQL Server - Qty INT and Price FLOAT. The values ​​for them are 1250 and 10.8601 - therefore, in order to get the total value of its Qty * Price and result: 13575.124999999998 (both in Java and SQL Server). It is right. The problem is that the client does not want to see this, they see this number only as 13575.125 and that he is. In one place they see it in 2 decimal places, and the other in 4 decimal places. When displayed in 4 decimal places, the number is correct - 13575.125, but when displayed in 2 decimal places, they consider it incorrect - 13575.12 - instead, it should be 13575.13!

Reference.

Answer 1

Your problem is that you use floats. On the java side, you need to use BigDecimal, not a float or double, and on the SQL side you need to use Decimal (19.4) (or Decimal (19.3) if this helps you get to your level of precision). Do not use the Money type, because the math in the Money type in SQL causes truncation, not rounding. The fact that the data is stored as a float type (which, as you say, is immutable) does not affect this, you just need to convert it first before doing the math.

In the specific example that you give, you first need to get 4 decimal precision number and put it in BigDecimal or Decimal (19,4) depending on the case, and then continue the round to 2 decimal precision. Then (if you round) you will get the desired result.

Answer 2

Use BigDecimal . Float is not a suitable type for presenting money. It will handle rounding correctly. Float will always produce rounding errors.

Answer 3

Floating point values ​​are not suitable for storing monetary amounts. From your description, I would probably treat the amounts as long integers, since the value of the money amount was multiplied by 10 ^ 5 as the database storage format.

You should be able to handle calculations with a sum that does not lose accuracy, so here again the floating point is not the path. If the total amount between debit and credit is turned off by 1 cent in the register, the ledger fails in the eyes of financial people, so make sure that your software works in your problem domain, not in yours. If you cannot use existing classes for monetary amounts, you need to create your own class that works with amount * 10^5 and formats in accordance with the accuracy required only for input and output.

Answer 4

Do not use the float data type for price. You must use Money or SmallMoney.

Here is the link for [MS SQL Datatypes] [1].

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

Correction: use decimal (19.4)

Thanks Ishay.

Answer 5

I think I see a problem.

10.8601 cannot be represented perfectly, and therefore, as long as rounding to 13575.125 works fine, it's hard to make it round to 0.13, because adding 0.005 is simply not quite available. And to make matters worse, 0.005 also has no exact idea, so you get a little less than 0.13.

Then your choice should round up twice, once to three digits, and then once to 2, or perform the best calculation to start with. Using a long or high-precision format, scale it to 1000 to get * .125 to * 125. Perform rounding using exact integers.

By the way, it’s not quite right to say that one of the endlessly repeating “floating point” variations is inaccurate or that it always causes errors. The problem is that the format can only represent fractions, which you can summarize negative powers to create two. Thus, from a sequence of 0.01 to 0.99, only 0.25, 0.50 and 0.75 have accurate representations. Therefore, FP is best used, ironically, by scaling it so that only integer values ​​are used, then it was as accurate as integer arithmetic of the data type. Of course, then you could just use fixed-point integers to start with.

Be careful scaling, say, from 0.37 to 37, until rounded. A floating point can be used for monetary values, but it works more than it costs, and, as a rule, the necessary experience is not available.

Answer 6

If you cannot fix the underlying database, you can fix java as follows:

 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 7

Although you should not store 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 problems because the results of the expression associated with it do not have dynamic scaling) and reveal this in the view of leaving SQL Server:

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

Answer 8

So, given that you cannot change the structure of the database (which would probably be a better option, given that you use unfixed precision to represent something that needs to be fixed / exactly, like so many others already), hope you can change the code somewhere. On the Java side, I think something like @andy_boot replied would work. On the SQL side, you will basically need to distinguish the inaccurate value to the highest precision you need, and continue to drop 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 9

You can also make DecimalFormat and then round 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 use Float as a type of DB field for storing currency.

Answer 10

If you cannot change the database to a fixed decimal data type, then you can try to round up by taking truncate ((x +.0055) * 10000) / 10000. Then 1.124999 will be “round” to 1.13 and will give consistent results. Mathematically, this is unreliable, but I think it will work in your case.

Answer 11

The FLOAT data type cannot represent fractions exactly because it is base2 instead of base10. (See Convenient link :) http://gregs-blog.com/2007/12/10/dot-net-decimal-type-vs-float-type/ ).

For financial calculations, or anything that requires a fraction representation, you must use the DECIMAL data type.