3

I'm using delphi 2010 with db2 9.7 Express-C and I have a database that has several fields of decimal type to work with monetary values. Now I see that there are some problems using it, like the 9.20 value displays the value 9.19999980926514 in my front-end. I need to change all the fields in my database to DECFLOAT or is there a function, property in tfield or other alternative to solve it ?

Thanks. Davis

Johan
  • 74,508
  • 24
  • 191
  • 319
Sammy Davis
  • 117
  • 4
  • 13
  • 1
    Live is [`being cruel`](http://pages.cs.wisc.edu/~rkennedy/exact-float?number=9.20) sometimes :-) So, what is the data type of your `TField` and your database column at this time ? Are you going to display only the value or you want to work with it somehow on the client side (like some calculations) ? Could you include this into your question ? – TLama May 06 '12 at 21:18
  • I've had my fair share of headaches with currencies and automatic rounding. I use straight integers, to the cent and keep in charge of rounding myself. – Pieter B May 07 '12 at 07:54
  • Which data access library are you using? dbExpress can access monetary field values returning the Currency data type using .AsCurrency – mjn May 07 '12 at 08:14

1 Answers1

3

working directly with monetary decimals is almost always a problem. due to different conversions made from the database to your front-end application, it is possible to lose or to gain(this applies to most of the financial systems also - see bankers rounding).


I suggest you to use the RoundTo function before making operations/display/etc. A very good article about rounding http://docwiki.embarcadero.com/RADStudio/XE2/en/Floating-Point_Rounding_Issues

Another suggestion will be using of the Currency type. Here is a question on SO with good explanation about this type How to avoid rounding problems when comparing currency values in Delphi?

Community
  • 1
  • 1
RBA
  • 12,337
  • 16
  • 79
  • 126
  • 1
    Solved. The error was being caused by the field type in the database. In the database was as real data type, not decimal. I changed to decimal (X, 2), now on the front-end is ok. And Delphi does not really recognize the type of field DECFLOAT. Thanks. Davis – Sammy Davis May 06 '12 at 22:56
  • If your database supports Delphi's Currency type, you really should use that. Some float numbers cannot be exactly represented in IEEE-754 floating binary point variables, e.g., 1.295 is represented as 1.29499 99999. If you must use float fields, take a look at John Herbsters rounding routines at http://cc.embarcadero.com/Item/21909. – Max Williams May 07 '12 at 01:32
  • @MaxWilliams - The DB2 `DECIMAL` and `NUMERIC` types are [Binary-Encoded decimal](http://en.wikipedia.org/wiki/Binary-coded_decimal) representations, so he's not in trouble there. – Clockwork-Muse May 07 '12 at 17:37