What is the best SQL data type for currency values? I'm using MySQL but would prefer a database independent type.
9 Answers
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.
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)
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.
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.
Though this may be late, but it will be helpful to someone else.From my experience and research I have come to know and accept decimal(19, 6).That is when working with php and mysql. when working with large amount of money and exchange rate
Related