In case you didn’t know, the most important part of my job is to prepare telephone bills for a small long-distance company (Cybertel). This means I spend most of my time manipulating data in databases, using either Perl or ASP or SQL (or occasionally Visual Basic).
Much of the data I have to deal with is dollar amounts – buy rate/sell rate of calls, payments and credits, total invoices, past due, commissions, etc. When you put information into a database, or in most programming languages, you must specify the data type. Is this particular piece of information going to be words? numbers? yes/no? And when you decide it’s going to be numbers, there are even more choices: INTEGER (“whole” numbers only: 2, 3000, -123; nothing to the right of the decimal point), FLOATING POINT (4, 4.1, -4.1234567, 5.64E-10 (scientific notation for .000000000564), FIXED (5.001, 3.222, -9.543 with a setting of 3 for decimal). There are actually a bunch more choices.
Well, obviously when you’re talking about dollars, you want x.yy – an integer plus two digits to the right of the decimal point. Four dollars and twenty cents is $4.20 not $4.2; and half of a quarter is 12 or 13 cents, not 12.5 cents. It doesn’t see anything wrong with telling me someone owes 1/3 of a dollar .33333333 unless I specifically tell it – and I have to explain how to DISPLAY as well as how to STORE the number (since this might be different).
Microsoft SQL, which we use for our primary database, has data types for INTEGER, FLOATING POINT, and even a special one called “MONEY”. Seems like that would be the one to choose, right? Actually, no – money STOREs FOUR digits of decimal; but DISPLAYs two digits!?!?! And the number of decimals stored for data type MONEY is not adjustable! So if a customer owes $4.21 (4.2100!) and I add a 10% late fee, the amount becomes $4.6310. And if I add up numbers with these wacky partial pennies I get bizarre results: .244 (“$0.24”) + .383 (“$0.38”) + .684 (“$0.68”) = 1.311 which might “display” as “$1.31” – which is just different enough from $1.30 to be annoying.
(By the way, this is commonly known as the “Superman” problem – because in one of the Superman movies, Richard Pryor was a programmer for a large company, who added up all the partial pennies left off from people’s salaries and added them to his own – and showed up at work the next day in a Ferarri.)
Why hasn’t Microsoft fixed this? As far as I can tell, this is the way it worked in Sybase which Microsoft bought, added pretty graphics to, and renamed SQL Server; and they haven’t bothered to fix it. And people like me aren’t willing to spend $500 on a support call to ask them to fix it, which they probably wouldn’t bother to do anyway. But I think next time I have to buy something from them I’d like to send tens of thousands of checks for $0.0049.
Perl, which I use for much of my bulk processing programming, is even worse (tho otherwise I love using perl!): it only knows “numeric” or “alpha”, so $1.456785 is fine (tho I can “sprintf” to display $1.46 if I want to but it still stores the entire number). To make matters worse, floating point numbers are stored in binary as integers plus calculated FRACTIONS!!! $4.20 is stored (hidden in binary) as 4 1/5. What this means is that if you add .70 + .15 you might get .84999999!
The solution? One of the data types I hadn’t considered, DECIMAL. DECIMAL lets me specify the number of digits to the right of the decimal. Unfortunately, unlike floating point, I also have to say how many digits to the left, too; so I have to guess what the largest number I might ever have is. Is it possible to have a $1000 phonecall? Maybe. Could a customer have amount due more than $100,000? I hope not, but it’s possible.
(The reason I’m ranting about this now is I’m waiting for my database to convert the cost of 5 million calls from MONEY to DECIMAL(10,2), and some of my bills last month were off by a penny – a tragedy of epic proportions!).
Don’t even get me started on NULL, because it’s 12:4 and time for lunch.