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.
+3
A:
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.
Marc Gravell
2009-03-06 08:31:20
+4
A:
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:
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).
Frederik Gheysels
2009-03-06 08:35:22
+8
A:
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!
Conrad
2009-03-06 08:37:15
Why not? What problems did you have using float?
Matt Hamilton
2009-08-31 01:21:30
Rounding errors most probably
nalply
2009-12-17 08:20:08
His application probably totalled up a few 200,000 amounts and ran out of digits
Chris S
2010-02-04 12:04:11
Floats cause very subtle rounding errors especially when doing calculations with lots of numbers. For financial applications this causes numbers not to balance
Conrad
2010-02-07 14:48:54
+1
A:
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>
AlSki
2009-03-06 09:18:22
A:
Use decimal and use more decimal places than you think you will need so that caclulations will be correct. Money does not alwys return correct results in calculations. Under no circumstances use float or real as these are inexact datatypes and can cause calculations to be wrong (especially as they get more complex).
HLGEM
2009-03-06 19:03:28