Hi,

I have heard numerous people saying that they had bad experiences with using the native Currency type or Numeric(15,4) type to store currency values in a database. They reported rounding errors, or not the correct rounding methods were used, display issues etc...

I have experienced similar issues with storing percentages with decimal values as Numeric(3,3) in a Firebird database. It displayed really weird values in the GUI in the scientific format eg: 2.516e+1

Their solution was to rather store currency values as integers - currency amount converted to cents. I have done similar for my percentage values and it works perfectly now. The Business objects handly the Rands, Dollar, Pounds (decimals) etc..., but when stored in the database, it's an integer value.

Anybody else been doing this? If so, over the long term, how have you found it. Workable - I guess I can't see why it wouldn't?

Regards,
- Graeme -

_______________________________________________
fpGUI - a cross-platform Free Pascal GUI toolkit
http://opensoft.homeip.net/fpgui/

I used Firebird SQL 2.x and use numeric(18,4) for Currency, i have no problem because Firebird work on numeric(18,4) as INT64 with special convert to try to get and put at the client, in fact Delphi/FPC and Firebird both internally take the Currency as Int64 but when convert it to print or to assign to another kind of variable, it divided to 10 or multiply with 10.

This is  a peace of my code
------
procedure TFBCSQLVAR.SetAsCurrency(Value: Currency);
begin
    if IsNullable then
      IsNull := False;
    FSQLVAR.sqltype := SQL_INT64 or (FSQLVAR.sqltype and 1);
    FSQLVAR.sqlscale := -4;
    FSQLVAR.sqllen := SizeOf(Int64);
    FSQLVAR.SetDataSize(0, FSQLVAR.sqllen);
    PCurrency(FSQLVAR.sqldata)^ := Value;
    Modified := True;
end;
function TFBCSQLVAR.GetAsCurrency: Currency;
...
case FSQLVAR.SqlDef of
    SQL_INT64:
       Result := FBScaleCurrency(PInt64(FSQLVAR.sqldata)^, FSQLVAR.sqlscale);
function FBScaleCurrency(Value: Int64; Scale: Integer): Currency;
var
  i: Integer;
begin
//Here we know 10000 in Int64 = 1 in Currency so
//if we have scale -4 that mean not need to * or / just tupe casting
//or make bias of -4 to correct the problem, this code must fast than
FBScaleDouble
  Scale := Scale + 4;
  if Scale > 0 then
  begin
    for i := 1 to Scale do
      Value := Value * 10;
  end
  else if Scale < 0 then
  begin
    for i := 1 to -Scale do
      Value := Value div 10;
  end;
  Result := PCurrency(@Value)^;
end;
---------------------

2009/5/30 Zaher Dirkey :

I used Firebird SQL 2.x and use numeric(18,4) for Currency, i have no problem because Firebird work on numeric(18,4) as INT64 with special convert to try to get and put at the client, in fact Delphi/FPC and

So basically you are also using Integer type to store currency. And then * or / to convert the 4 digit decimals.

What I didn't mention in my previous post is that recurring decimals (eg. 1.3333333....) are a real pain when storing as standard floating point types and causes all kinds of rounding errors in calculations. We have no decided to trunc our currency values to 2 decimals and use our own rounding routine (not bankers rounding etc...). When then convert our currency amounts to integers before they are stored in the database.

So far this has been keeping use (developers) happy and our company accountants are also happy with the results. :-)

Regards,
- Graeme -

_______________________________________________
fpGUI - a cross-platform Free Pascal GUI toolkit
http://opensoft.homeip.net/fpgui/

I have no problem with rounding and floating point with currency because in whale my project (also Accounting) use Currency variable not other float variable so that make compatibility with my project and my database, only i use the float type for money converting between currencies.

We have no decided to trunc our currency values to 2 decimals and use our own rounding routine (not bankers rounding etc...).

Calculations with amounts of money frequently are subject to detailed official prescreptions, e.g. for tax calculations. There exists no way to unify all those national or otherwise specified procedures in an standard data type. So it's common practice to implement the prescriptions in according integer calculations with the prescribed scaling and rounding. <sigh>

DoDi