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
Some applications/tools are using Integers to Store money/currency values. Similar built-in implementation for such Currency/Money Data Types is available in MS SQL Server and also in Visual Basic For Applications. In such data types, data is stored as integer only (no decimal point is stored). Some fix number of last digits say 2/3/4 are assumed for decimal portion. SQLite does not have built-in currence/money data type. But integer can be used to store monetory values in similar way and handle related calculations using similar concept. Will need you to handle decimal portion (fix number of digits) properly while storing/retrieving as well as in calculations. Hope this helps. Rohit Reference Info from MSDN Library Microsoft SQL Server 2000 -> money and smallmoney data types Monetary data - represents positive or negative amounts of money. In Microsoft SQL Server 2000, monetary data is stored using the money and smallmoney data types. Monetary data can be stored to an accuracy of four decimal places. Use the money data type to store values in the range from -922,337,203,685,477.5808 through +922,337,203,685,477.5807 (requires 8 bytes to store a value). Use the smallmoney data type to store values in the range from -214,748.3648 through 214,748.3647 (requires 4 bytes to store a value). If a greater number of decimal places are required, use the decimal data type instead. Visual Basic For Applications -> Currency Data Type Currency variables are stored as 64-bit (8-byte) numbers in an integer format, scaled by 10,000 to give a fixed-point number with 15 digits to the left of the decimal point and 4 digits to the right. This representation provides a range of -922,337,203,685,477.5808 to 922,337,203,685,477.5807. The type-declaration character for Currency is the at sign (@). The Currency data type is useful for calculations involving money and for fixed-point calculations in which accuracy is particularly important. 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 > -- View this message in context: http://www.nabble.com/Storing-monetary-values-and-calculations-tf4264034.html#a12323492 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 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) ? It was not added to Sqlite releases. We added the DECIMAL type using the regular DECIMAL(n,m) notation defining precision and scale. It takes advantage of the Sqlite declared type logic, but needed a slight change to stop Sqlite treating these columns as numeric and flipping them to floating point storage. We did not integrate the decimal arithmetic into the regular SQL arithmetic, instead defining seperate decimal functions and aggregate functions. We were using Sqlite for a commercial system and needed an appropriate fixed point number format and accurate arithmetic for money values. 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] - - 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] -
[sqlite] Storing monetary values and calculations
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 -- View this message in context: http://www.nabble.com/Storing-monetary-values-and-calculations-tf4264034.html#a12135202 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
