### Re: [sqlite] Storing monetary values and calculations

This is not available as publicly distributed, but there is some code in http://www.viacognis.com/declib which you are free to use. RohitPatel wrote: Hi John If sourcecode for that type (DECIMAL) is available for public, please let us know the link and we can explore it for SQLite. Thanks John Stanton wrote: That is an interesting way to store money. We developed a fixed point arithmetic library of arbitrary precision using the algorithms described by Knuth in his semi-numerical algorithms volume and using standard DECIMAL(n,m) definition. Rounding is precise using an algorithm which does not drift and intermediate. Finally the numbers are stored in display format, right justified and with leading spaces and signs so that they can be displayed in HTML pages or printed output without reformating. ... ... ... - To unsubscribe, send email to [EMAIL PROTECTED] -

### Re: [sqlite] Storing monetary values and calculations

Hi John If sourcecode for that type (DECIMAL) is available for public, please let us know the link and we can explore it for SQLite. Thanks John Stanton wrote: > > That is an interesting way to store money. We developed a fixed point > arithmetic library of arbitrary precision using the algorithms described > by Knuth in his semi-numerical algorithms volume and using standard > DECIMAL(n,m) definition. Rounding is precise using an algorithm which > does not drift and intermediate. Finally the numbers are stored in > display format, right justified and with leading spaces and signs so > that they can be displayed in HTML pages or printed output without > reformating. > ... > ... > ... > -- View this message in context: http://www.nabble.com/Storing-monetary-values-and-calculations-tf4264034.html#a12401463 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -

### Re: [sqlite] Storing monetary values and calculations

That is an interesting way to store money. We developed a fixed point arithmetic library of arbitrary precision using the algorithms described by Knuth in his semi-numerical algorithms volume and using standard DECIMAL(n,m) definition. Rounding is precise using an algorithm which does not drift and intermediate. Finally the numbers are stored in display format, right justified and with leading spaces and signs so that they can be displayed in HTML pages or printed output without reformating. The arithmetic is obviously not suited to intensive calculation but is well suited to money usage where the absence of radix changes and edit conversions more than compensates for the arithmetic overhead, helped by using effcient arithmetic algorithms. Another advantage is that it makes inserting numbers in report tables absolutely trivial. They automatically line up in right justified columns. It was a fairly simple exercise to graft this number system into Sqlite, really only requiring that the regular behaviour of giving a DECIMAL type numeric affiliation be altered to text affiliation to stop Sqlite from translating the formatted, fixed point numbers to floating point. RohitPatel wrote: While doing currency math, a useful money class at following link, may be used as a a reference. http://www.di-mare.com/adolfo/p/money.htm Yet Another C++ Money Class (by Adolfo Di Mare), The C Users Journal, Vol.10 No.4, pp [58-64], April 1992 Rohit. - To unsubscribe, send email to [EMAIL PROTECTED] -

### Re: [sqlite] Storing monetary values and calculations

While doing currency math, a useful money class at following link, may be used as a a reference. http://www.di-mare.com/adolfo/p/money.htm Yet Another C++ Money Class (by Adolfo Di Mare), The C Users Journal, Vol.10 No.4, pp [58-64], April 1992 Rohit. -- View this message in context: http://www.nabble.com/Storing-monetary-values-and-calculations-tf4264034.html#a12333186 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -

### Re: [sqlite] Storing monetary values and calculations

--- Mikey C <[EMAIL PROTECTED]> wrote: > John Stanton wrote: > > We have > > added a DECIMAL type to Sqlite. This stores fixed point numbers in > > ASCII display format and performs accurate artithmetic and presents > > nicely from HTML. > > In which release was the DECIMAL affinity added to SQLite? Can you specify > the precision and scale as per SQL-92 standard? e.g. DECIMAL(9,2) ? There is no support for an SQL-92 DECIMAL data type. He is describing a convention he is using in his code. Choose the right car based on your needs. Check out Yahoo! Autos new Car Finder tool. http://autos.yahoo.com/carfinder/ - To unsubscribe, send email to [EMAIL PROTECTED] -

### Re: [sqlite] Storing monetary values and calculations

John Stanton wrote: > > We have > added a DECIMAL type to Sqlite. This stores fixed point numbers in > ASCII display format and performs accurate artithmetic and presents > nicely from HTML. > In which release was the DECIMAL affinity added to SQLite? Can you specify the precision and scale as per SQL-92 standard? e.g. DECIMAL(9,2) ? John Stanton wrote: > > If you store money as an integer with an "implied decimal point" (a > familiar method for old-time COBOL programmers) it will work. We have > added a DECIMAL type to Sqlite. This stores fixed point numbers in > ASCII display format and performs accurate artithmetic and presents > nicely from HTML. > > Floating point numbers for money is a perennial trap for young players. > > Mikey C wrote: >> Hi there, >> >> Currently I am using a SQLite 3.x database that stores and calculates >> currency values using the column type NUMERIC (which I believe has a >> FLOAT >> affinity). >> >> However this leads to errors in storing values values in floating point >> representation. >> >> I guess there is no planned support for direct fixed point types, so what >> is >> the best approach? >> >> Store the monetary values in an INTEGER column and multiply all values up >> by >> 100 to store in pence/cents? >> >> Any advice? >> >> Thanks, >> >> Mike > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > -- View this message in context: http://www.nabble.com/Storing-monetary-values-and-calculations-tf4264034.html#a12140213 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -

### Re: [sqlite] Storing monetary values and calculations

If you store money as an integer with an "implied decimal point" (a familiar method for old-time COBOL programmers) it will work. We have added a DECIMAL type to Sqlite. This stores fixed point numbers in ASCII display format and performs accurate artithmetic and presents nicely from HTML. Floating point numbers for money is a perennial trap for young players. Mikey C wrote: Hi there, Currently I am using a SQLite 3.x database that stores and calculates currency values using the column type NUMERIC (which I believe has a FLOAT affinity). However this leads to errors in storing values values in floating point representation. I guess there is no planned support for direct fixed point types, so what is the best approach? Store the monetary values in an INTEGER column and multiply all values up by 100 to store in pence/cents? Any advice? Thanks, Mike - To unsubscribe, send email to [EMAIL PROTECTED] -