Output in currency format

[Peter Haworth]

I have a column defined with a type of FLOAT, which I believe is
treated as REAL by SQLite. When selecting that column, I would like
it to be returned with a leading "$" sign and always have a decimal
point and two numbers after the decimal point. I can use
concatenation to get the "$" sign in there but have not been able to
enforce the inclusion of a decimal point and two decimal places.
Whole numbers are returned with no decimal point and no decimal
places. Numbers where the second decimal place would be a zero are
returned with the decimal point but only one decimal place.

I tried using the round function specifying 2 decimal places but this
does not affect the output. I have also tried using CAST to force the
column into various other types but that also does not affect the
output.

Web searches suggest that the CONVERT function is available in other
SQL implementations but I don't see that anywhere in the SQLite
documentation. Is there a way to do this or should I plan on handling
it within the application?

Thanks

Pete Haworth

[Pavel Ivanov]

There's no way to force SQLite to return exactly 2 decimal places for
you. You have to do it in your application or if you really-really
need to do it in sql you can do it like this (assuming you need column
col from table tab):

select '$'||case
when length(col) = 1
then '0.0'||col
when length(col) = 2
then '0.'||col
else
substr(col, 1, length(col) - 2)||'.'||substr(col, length(col) - 1)
end as col
from (select cast(round(col * 100) as text) as col from tab)


But I wouldn't do that if I were you. ;-)


Pavel

Post by Peter Haworth
I have a column defined with a type of FLOAT, which I believe is
treated as REAL by SQLite.  When selecting that column, I would like
it to be returned with a leading "$" sign and always have a decimal
point and two numbers after the decimal point.  I can use
concatenation to get the "$" sign in there but have not been able to
enforce the inclusion of a decimal point and two decimal places.
Whole numbers are returned with no decimal point and no decimal
places.  Numbers where the second decimal place would be a zero are
returned with the decimal point but only one decimal place.
I tried using the round function specifying 2 decimal places but this
does not affect the output.  I have also tried using CAST to force the
column into various other types but that also does not affect the
output.
Web searches suggest that the CONVERT function is available in other
SQL implementations but I don't see that anywhere in the SQLite
documentation.  Is there a way to do this or should I plan on handling
it within the application?
Thanks
Pete Haworth
_______________________________________________
sqlite-users mailing list
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Rich Shepard]

Is there a way to do this or should I plan on handling it within the
application?

Pete,

The latter. Display formatting is not part of SQL.

You might also consider using integer values for money because the math is
more accurate.

Rich

[Roger Binns]

Post by Peter Haworth
I have a column defined with a type of FLOAT, which I believe is
treated as REAL by SQLite. When selecting that column, I would like
it to be returned with a leading "$" sign and always have a decimal
point and two numbers after the decimal point.

It is a spectacularly bad idea to use floating point for currency. Numbers
that look simple to us humans such as .01 and .10 are truncated recurring
fractions in the binary representation used by the underlying hardware and
will introduce errors in your calculations.

This page helps show the details:

http://docs.python.org/tutorial/floatingpoint.html

Roger

[Peter Haworth]

Seems like I should handle the formatting in my application. Not sure
I agree that sqlite is not the place to do output formatting - it
provides lots of date and time formatting features so at least in that
area, output formatting is available.

Thanks also for the info re accuracy/REAL formatting. I will change
my db design accordingly.

Thanks,

Pete Haworth

[P Kishor]

Seems like I should handle the formatting in my application.  Not sure
I agree that sqlite is not the place to do output formatting - it
provides lots of date and time formatting features so at least in that
area, output formatting is available.

A wee bit of error in time formatting and display usually will not
amount to a hill of beans, but make an error in the number of pennies
owed to someone and all hell will break loose.

Thanks also for the info re accuracy/REAL formatting.  I will change
my db design accordingly.
Thanks,
Pete Haworth
_______________________________________________
sqlite-users mailing list
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

--
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
-----------------------------------------------------------------------
Assertions are politics; backing up assertions with evidence is science
=======================================================================
Sent from Madison, Wisconsin, United States

[Fred Williams]

The best (safest?) way I have found to handle non scientific math is to
work strictly with integers and multiply and divide using ROUND/TRUNC as
required to gain the precision required. This includes way more than
SQLite situations as well. Borland (Code Gear) seem to be the only
developer tools producer to recognize the need for "business" math with
their native BCD data type.

Fred

Post by Peter Haworth
Seems like I should handle the formatting in my application. Not sure
I agree that sqlite is not the place to do output formatting - it
provides lots of date and time formatting features so at least in that
area, output formatting is available.
Thanks also for the info re accuracy/REAL formatting. I will change
my db design accordingly.
Thanks,
Pete Haworth
_______________________________________________
sqlite-users mailing list
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Simon Slavin]

Post by Fred Williams
The best (safest?) way I have found to handle non scientific math is to
work strictly with integers and multiply and divide using ROUND/TRUNC as
required to gain the precision required. This includes way more than
SQLite situations as well.

Another aspect of the problem is where to convert from integers to currency: inside SQL, in library routines, or in each application. At the moment my preference is that all numbers inside SQL are integers. No part of the SQL system knows anything but integer English currency, integer Euro currency, integer US currency, etc.. Conversion between integers and currency format is always done in surrounding software. But there are arguments for doing it in other ways.

Simon.

[Peter Haworth]

Thanks for all the advice on this. Just to be clear, I wasn't
referring to the accuracy of calculations when I compared the sqlite
date/time formatting capabilites to the lack of similar functionality
for currency, just the fact that there is a precedent for sqlite
providing output formatting capability for some types of data.

Just to be sure I get my calculations correct, the suggestion is that
all currency amounts should be stored in the database as whole numbers
in fields of type INTEGER. Calculations would be done using the whole
numbers and I'll need some routines to convert between the database
format and the display format. Do I have that right?

Pete Haworth

[Fred Williams]

That has always been my most effective last resort when attempting to do
business math with many databases and development environments.

Post by Peter Haworth
Thanks for all the advice on this. Just to be clear, I wasn't
referring to the accuracy of calculations when I compared the sqlite
date/time formatting capabilites to the lack of similar functionality
for currency, just the fact that there is a precedent for sqlite
providing output formatting capability for some types of data.
Just to be sure I get my calculations correct, the suggestion is that
all currency amounts should be stored in the database as whole numbers
in fields of type INTEGER. Calculations would be done using the whole
numbers and I'll need some routines to convert between the database
format and the display format. Do I have that right?
Pete Haworth
_______________________________________________
sqlite-users mailing list
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Simon Slavin]

Post by Peter Haworth
Just to be sure I get my calculations correct, the suggestion is that
all currency amounts should be stored in the database as whole numbers
in fields of type INTEGER. Calculations would be done using the whole
numbers and I'll need some routines to convert between the database
format and the display format. Do I have that right?

Exactly. I'm familiar with lots of big pieces of bank software and that's how they do it.

There's still some possibility for confusion, however: how many places of decimals do you use for each currency ? As far as I know, no currently traded currency uses more than two digits of precision. Some systems use two digits for dollars (because that's the actual smallest unit you can trade in it). But some use four digits for every currency because it makes the library routines simpler, betrays rounding problems in your code, and allows for figures to be rounded later. It's one of the unsettled problems of financial software.

Since you probably won't have to deal with multi-currency rounding problems I'd recommend you stick to two digits unless advised not to by someone who knows exactly what you're doing.

Simon.

[Nicolas Williams]

Post by Simon Slavin
There's still some possibility for confusion, however: how many places
of decimals do you use for each currency ? As far as I know, no
currently traded currency uses more than two digits of precision.

^^^^^^^^^^^^^^^^^^^^^^^
They use integer math to avoid floating point rounding issues, but
logically those integers are still real (or at least rational) numbers,
and so we can speak of base, mantissa and exponent. The precision
required is pretty large, much more than two digits.

Consider U.S. dollars, where we need to express from some fraction of
pennies to tens of trillions of dollars. That's at least 16 digits of
precision. You need to use larger than 32-bit integers for this,
meaning, in practice, 64-bit integers. Besides, 100 years ago 16 digits
of precision for counting money would probably have seemed farfeteched,
so use 64-bit integers, which gets you a bit less than 20 digits of
precision. (Hmmm, 19 digits looks a bit small now!)

Nico
--

[Simon Slavin]

Post by Nicolas Williams

Post by Simon Slavin
There's still some possibility for confusion, however: how many places
of decimals do you use for each currency ? As far as I know, no
currently traded currency uses more than two digits of precision.

^^^^^^^^^^^^^^^^^^^^^^^
They use integer math to avoid floating point rounding issues, but
logically those integers are still real (or at least rational) numbers,
and so we can speak of base, mantissa and exponent. The precision
required is pretty large, much more than two digits.

Integers in all languages I'm aware of are not stored as mantissa/exponent, they're stored as bits with complete precision. You can say you need a particular number of bits, but you'll never lose the last bit (the 1s) just because your numbers have got too big. You'll get an overflow error instead.

By 'two digits of precision' I was referring to cents for US dollars, pence for pounds sterling, etc.. Some currencies have no fractional part (e.g. Yen). And I was sure there were ... ah, here we are:

http://www.worldatlas.com/aatlas/infopage/currency.htm

Okay, so do not hardwire your code for two decimal places because you won't handle Kuwaiti dinar correctly.

Simon.

[Roger Binns]

Post by Simon Slavin
Integers in all languages I'm aware of are not stored as mantissa/exponent, they're stored as bits with complete precision.

There is one huge exception I found out the hard way recently: Javascript
stores all 'integers' as floating point and so Javascript integers lose
precision. For example if you try to use 9223372036854775807 in Javascript
it will keep coming back as 9223372036854776000.

Roger

[Simon Slavin]

Post by Roger Binns

Post by Simon Slavin
Integers in all languages I'm aware of are not stored as mantissa/exponent, they're stored as bits with complete precision.

There is one huge exception I found out the hard way recently: Javascript
stores all 'integers' as floating point and so Javascript integers lose
precision. For example if you try to use 9223372036854775807 in Javascript
it will keep coming back as 9223372036854776000.

JavaScript doesn't have an integer type, just a number type:

var myVariable = 42
document.writeln ( "myVariable is a " + typeof myVariable )

Simon.

[Roger Binns]

You are agreeing with me :-)

Roger

[Dan Bishop]

Post by Simon Slavin

Post by Roger Binns

Post by Simon Slavin
Integers in all languages I'm aware of are not stored as mantissa/exponent, they're stored as bits with complete precision.

There is one huge exception I found out the hard way recently: Javascript
stores all 'integers' as floating point and so Javascript integers lose
precision. For example if you try to use 9223372036854775807 in Javascript
it will keep coming back as 9223372036854776000.

var myVariable = 42
document.writeln ( "myVariable is a " + typeof myVariable )

Microsoft Excel has a similar problem. I ran into it back when I was
working in a credit union and tried to import a CSV file containing
credit card numbers. Wouldn't have noticed except that credit card
numbers are 16 digits long and double only has 15 digits of precision.

[Jean-Denis Muys]

Post by Dan Bishop
Microsoft Excel has a similar problem. I ran into it back when I was
working in a credit union and tried to import a CSV file containing
credit card numbers. Wouldn't have noticed except that credit card
numbers are 16 digits long and double only has 15 digits of precision.

Well in that case and for once, I must say the problem is not Excel's, it's
yours: credit card "numbers" are not numbers at all, but strings, that just
happen to use digits only. You should have imported them as text, not as
numbers. Excel had no way to guess.

Jean-Denis

[Rich Shepard]

Post by Dan Bishop
Microsoft Excel has a similar problem. I ran into it back when I was
working in a credit union and tried to import a CSV file containing credit
card numbers. Wouldn't have noticed except that credit card numbers are
16 digits long and double only has 15 digits of precision.

Excel also has an incorrect formula for Net Present Value. Lotus 1-2-3 had
an incorrect formula for standard deviation (they used the population
formula rather than the sample formula).

I don't use any M$ software, but when I need to import large numbers into
a spreadsheet (I use XessSE), it's always as text.

Rich

[Nicolas Williams]

Post by Simon Slavin

Post by Nicolas Williams

Post by Simon Slavin
There's still some possibility for confusion, however: how many places
of decimals do you use for each currency ? As far as I know, no
currently traded currency uses more than two digits of precision.

^^^^^^^^^^^^^^^^^^^^^^^
They use integer math to avoid floating point rounding issues, but
logically those integers are still real (or at least rational) numbers,
and so we can speak of base, mantissa and exponent. The precision
required is pretty large, much more than two digits.

Integers in all languages I'm aware of are not stored as
mantissa/exponent, they're stored as bits with complete precision.

That's why I wrote "logically". Clearly, very, very clearly, int64_t
is not a float, has no mantissa, no exponent. But you can use it as
though an int64_t were a real (well, rational) number.

Post by Simon Slavin
You can say you need a particular number of bits, but you'll never
lose the last bit (the 1s) just because your numbers have got too big.
You'll get an overflow error instead.

Yes.

Post by Simon Slavin
By 'two digits of precision' I was referring to cents for US dollars,
pence for pounds sterling, etc.. Some currencies have no fractional

Tenths of pennies are also used though. See just about any gas station
in the U.S.

Oh, I forgot, with 64-bit _signed_ ints you get one fewer digit of
precision than I wrote earlier.

Nico
--

[Peter Haworth]

Thanks for all the comments on this. Didn't realise there were so
many things to worry about when dealing with currency!

The system I'm developing is only dealing with US dollars right now
but I would hope it might make it's way into other countries at some
point. Even with dollars, I will have to deal with more than two
decimal places though. The application is aimed at independant music
bands to manage their businesses and one of the things it does is
import data from places like iTunes, Rhapsody, etc whose royalty
payments commonly extend to three decimal places or more.

That often give rise to some rounding issues. I do all the math using
however many decimal places are given to me and then round the total
to two decimal places, then calculate how much is owed to each band
member based on that total. But whoever writes the checks for some of
the above outfits doesn't seem to use the same logic since the checks
I get are often different than what I calculate by a few pennies
(usually more than the total I calculate). Not really a big deal but
it does require some otherwise-unnecessay accounting entries to deal
with the rounding errors.


Pete Haworth

[Simon Slavin]

Post by Peter Haworth
That often give rise to some rounding issues. I do all the math using
however many decimal places are given to me and then round the total
to two decimal places, then calculate how much is owed to each band
member based on that total. But whoever writes the checks for some of
the above outfits doesn't seem to use the same logic since the checks
I get are often different than what I calculate by a few pennies
(usually more than the total I calculate). Not really a big deal but
it does require some otherwise-unnecessay accounting entries to deal
with the rounding errors.

Whether or not you keep rounding fractions for yourself, be careful that the bands have agreed on how you do rounding. Also, have a tax accountant check out that your method of rounding is acceptable by whatever tax authorities you deal with. Oh yeah, and sort out what tax calculations you're going to have to do before you write the system.

I once wrote a system that dealt with stock events for many different types of stock and it had to use one type of rounding to work out how much money to give the stockholder and another type of rounding to work out how much tax to pay. My customer was allowed to keep any difference between the two (which was always positive). Horribly complicated system to write but it generated a few pence profit every few seconds just by doing what the taxman said.

Simon.