Alan Gutierrez

Getting back to currency. The answer is simple. sprintf('%.2f', 10.8398274982734). Except it’s wrong.

That f in '%2f' is going to get you.

Whatever ActiveRecord says about currency, MySQL does not have a currency type. It does have a decimal type, however, and I’m not sure how well that maps to the ActiveRecord currency type, because currency is a sticky wicket.

You cannot use binary floating point numbers, like the kind you’ll use by default in most computer languages, because they are binary, and when you print the decimal representation, it is going to be off by a millismidge. You can assign a value to a floating point number, and ask for it right back, and find that is is off by a wee wee bit.

That is why there are decimal types that do the arithmetic we learned in grade school. They carry that one to the ten’s place, because to drop it is to short change a pensioner somewhere, and won’t that look bad. Every penny counts!

Actually, I don’t know how they implement arbitrary precision decimal types. Paul Christmann does, and he’ll tell us all about it in the comments.

Via Google, I find a very nice article on currency at CPAN in the documenation for Math::Currency, which is a Perl module, of course. The description section nails it.

There was a rambling discussion of why binary floats don’t work for currency in a thread on comp.lang.ruby called [Using Floats for Currency]. You can color on the screen with crayons while you read it.

That thread coughs up a Money class to play with.

The long and the short of it is, most database engines have a decimal type, they may call it currency, or they may call it decimial, or they may call it George, and they may hug it and squeeze it, but they are all very much not the same, so read the documentation for your particular database engine.

This advice repeated, in this article on OnLamp.com called SQL Data Types. Here’s a story about using the decimal type in MySQL that shows you how to use it through someone else’s trial and error.

At times, in my XSLT doodles, I’ve found myself without a decimal type, but with a need to tally currency.

The simplest way to do so, and to maintian precision, was to look at the amounts as a count of pennies. Here are some C programmers talking about the pennies trick. One dollar is represented as the integer 100. Credits, debits and summations turn out A-OK.