• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1604
  • Last Modified:

Delphi Field Setting using AsFloat()

Has anyone had issues with using AsFloat for SQL Server Decimal Data types?

We seem to be having a rounding issue with Decimal

Precision 28
Scale 9
Length 13

having values put in through the system we have as 10.245000000 and showing up in the db as 10.244999999
or 10.245000001

Any known fixes or suggestions would be very much appreciated.

  • 2
1 Solution
you cold try asCurrency or a bcd-field
Binary floating point representations of decimal numbers are not always "true" representations.  Powers of two (1/2, 1/4, 1/16, etc) represent well in binary, but powers of 10 do not.

245/1000 is 11110101/1111101000, which is really nasty in binary.

If accurate representation of base 10 fractions is critical, then BCD representations such as currency might be better.  This holds true for your Delphi code as well.

Another possibility is a fixed point representation.  these are stored as integers, and scaling factors are applied to all multiplications and divisions.
your problem is, that the precicion of your db field is too high for any float type

.AsFloat returns a double value wich has 15-16 significant digits you are having 28. The "funny" values occur when "downgrading" float values.

Currency seems not to be a apropiate solution if you really need a scale of 9, because currency will store/return only a scale of 4 digits.

After that waht the onlinehelp is telling, a BCD Field might be the solution, but i never tried it.

Finally there is probably another solution if you don't need the precision of 28 digits you might use float as datatype in the SQL Server.
If you really need this precision take a closer look at the BCD fields Kretzmar suggested
I didn't read very closely ... scale = 9 forces a change of strategy.

I would recommend processing as Int64 and applying scaling factors in multiply, divide, and display operations.

All other operations are standard Int64 operations.

ScaledAdd and ScaledSub are just straight Add and Sub, since addition and subtraction don't alter the scale of the representation.

Your will need ScaledMul, ScaledDiv, ScaledToString, and ScaledFromString methods.  ScaledMul will divide the result safely by the scaling factor (1*10^9), and ScaledDiv will multiply its result safely by the scaling factor.  "Safely" means "giving the correct answer to the intended operation".  You may have to do some 128 bit math to guarantee this.

ScaledToString will identify the whole and fractional components and express them as strings in base 10 decimal notation, and ScaledFromString will work the other way.  The scaling factor is applied as a multiplier for ToString, and as a divisor for FromString.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now