Rounding returned float values from a DB to their

Question

I've got a database column that contains (for some reason lost in the midst of time) float values.

So, someone populates a DB table with some small value, say 0.00025, and when I run a report I actually get back 0.000249999999....or somesuch, due to the inability to represent the entered value.

Is there any reliable way to take the float value and convert back to what was originally entered?

DB is Sybase.

Answer 1

Is there any reliable way to take the float value and convert back to what was originally entered?

No. There are roughly, ummm, a boat load of real values that might map to a single floating-point value. The db stores the closest FP approximation to the original entry; there are, in the general case, an infinite number of real values for which 0.000249999999... is the closest FP approximation.

And again, in the general case, you can't safely make assumptions about the length of the entry. That is, you can't safely tell by looking at 0.000249999999 whether the user typed that in within an application program, whether the user typed .00025, whether a dba updated that column to the result of a calculation (in which case the "original" value might be less than 0.0002499999etc) and so on and so on.

But, if you know how many digits you want to see to the right of the decimal point, you can use Sybase's ROUND() function.

Answer 2

No, this is the behavior of floating-point numbers (you sacrifice accuracy for capacity or efficiency). While you can use rounding to achieve approximations, if you require exact storage up to a particular level of precision, you should use decimal.

Answer 3

That would be 0.0002498626708984375....

This comes down to "Know your data". How do you know that the correct value was not 0.0002498626708984375? Likely because you know how many decimals it was originally specified to.

Use that knowledge to solve the problem, by casting to NUMERIC(8, 5) or whatever the appropriate solution is.