What is is the best SQL data type for currency values? I'm using MySQL but would prefer a database independent type.
+15
A:
Something like Decimal (19,4) usually works pretty well in most cases. You can adjust the scale and precision to fit the needs of the numbers you need to store. Even in SQL Server, I tend not to use "money" as it's non standard.
Kibbee
2009-03-10 01:38:56
A point about the size: according to MSDN (http://msdn.microsoft.com/en-us/library/ms187746.aspx), Decimal(10,4) and Decimal(19,4) both use 9 bytes of storage, so might as well spring for that extra 9 digits of scale.
Adam Nofsinger
2010-03-24 14:50:19
+1
A:
Assaf's response of
Depends on how much money you got...
sounds flippant, but actually it's pertinant.
Only today we had an issue where a record failed to be inserted into our Rate table, because one of the columns (GrossRate) is set to Decimal (11,4), and our Product department just got a contract for rooms in some amazing resort in Bora Bora, that sell for several million Pacific Francs per night... something that was never anticpated when the database schema was designed 10 years ago.
Update: If anyone is interested, the location is the St Regis Bora Bora Resort, and if you're interested in booking a weeks holiday over Christmas, then drop me an email, because I'm sure I could take a year off from the commission I'd make on that sale!
Scott Ferguson
2009-03-10 01:45:04
Which is why I recommended decimal (19,4). May sound like overkill, but you never know when you'll need to store a really large amount in that field.
Kibbee
2009-03-10 01:49:53
@Kibbee: I wouldn't have agreed with you for our requirements until today. (For 6 years (11,4) was perfectly fine...)
Scott Ferguson
2009-03-10 01:54:24
+5
A:
The only thing you have to watch out for is if you migrate from one database to another you may find that DECIMAL(19,4) and DECIMAL(19,4) mean different things
( http://dev.mysql.com/doc/refman/5.1/en/precision-math-decimal-changes.html )
DBASE: 10,5 (10 integer, 5 decimal)
MYSQL: 15,5 (15 digits, 10 integer (15-5), 5 decimal)
SeanJA
2009-03-10 02:06:30
+3
A:
It is also important to work out how many decimal places maybe required for your calculations.
I worked on a share price application that required the calculation of the price of one million shares. The quoted share price had to be stored to 7 digits of accuracy.
Leah
2009-03-10 02:34:09
It's a good point - especially in financial apps, "price" most certainly doesn't imply "money"
Mike Woodhouse
2009-03-10 08:56:02