Delphi Field Setting using AsFloat()

Posted on 2003-03-05
Medium Priority
Last Modified: 2008-02-01
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.

Question by:ddudley3
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 27

Expert Comment

ID: 8073510
you cold try asCurrency or a bcd-field

Expert Comment

ID: 8074650
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.

Accepted Solution

sfock earned 400 total points
ID: 8075231
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

Expert Comment

ID: 8075384
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses
Course of the Month7 days, 21 hours left to enroll

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question