Menu
I need to store a couple of money related fields in the database but I'm not sure which data type to use between money and decimal.
569
Which datatype is best suited to store currency values? Explanation: Currency is a numeric information. For monetary calculations, FLOAT and DOUBLE are subject to rounding error and may not be suitable. A DECIMAL(M, 2) type is best suited for it.
Inside a database, data is stored into tables. As we mentioned in the previous post, the S in SQL stands for structured. This means that all the data has to be stored in a standardized manner. This is why tables have been created. Tables are the simplest objects (structures) for data storage that exist in a database.
We can store the money values in MySQL in decimal(value1,value2). Here, value1 is the total range including value2. The value2 specifies the number of digits after the decimal point.
Money is stored in the same way an integer is stored, whereas decimal is stored as a decimal point and decimal digits. This means that money will drop accuracy in most cases, while decimal will only do so when converted back to its original scale.
Decimal and money ought to be pretty reliable. What i can assure you (from painful personal experience from inherited applications) is DO NOT use float!
133
I always use Decimal; never used MONEY before.
Recently, I found an article regarding decimal versus money data type in Sql server that you might find interesting:
Money vs Decimal
It also seems that the money datatype does not always result in accurate results when you perform calculations with it : click
What I've done as wel in the past, is using an INT field, and store the amount in cents (eurocent / dollarcent).
30
I guess it comes down to precision and scale. IIRC, money is 4dp. If that is fine, money expresses your intent. If you want more control, use decimal with a specific precision and scale.
30
It depends on your application!!! I work in financial services where we normally consider price to be significant to 5 decimal places after the point, which of course when you buy a couple of million at 3.12345pence/cents is a significant amount. Some applications will supply their own sql type to handle this.
On the other hand, this might not be necessary. <Humour> Contractor rates always seemed to be rounded to the nearest £100, but currently seem to be to nearest £25 pounds in the current credit crunch. </Humour>
30
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
