31

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.

Elijah
  • 13,368
  • 10
  • 57
  • 89
ntombela
  • 1,357
  • 2
  • 19
  • 31

7 Answers7

45

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!

Rad
  • 8,336
  • 4
  • 46
  • 45
  • 2
    Why not? What problems did you have using float? – Matt Hamilton Aug 31 '09 at 01:21
  • 2
    His application probably totalled up a few 200,000 amounts and ran out of digits – Chris S Feb 04 '10 at 12:04
  • 24
    Floats cause very subtle rounding errors especially when doing calculations with lots of numbers. For financial applications this causes numbers not to balance – Rad Feb 07 '10 at 14:48
  • 2
    It's worth nothing that although your database may support accuracy with DECIMAL, your programming language may not. For example, PHP will use float and break everything. In that instance, you'd probably want to use INTEGER. – Stephen Melrose Oct 01 '15 at 20:54
14

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).

Community
  • 1
  • 1
Frederik Gheysels
  • 56,135
  • 11
  • 101
  • 154
11

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
  • 1,026,079
  • 266
  • 2,566
  • 2,900
4

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
  • 6,868
  • 1
  • 26
  • 39
2

Don't align your thoughts based on available datatypes. Rather, analyze your requirement and then see which datatype fits best. Float is anytime the worst choice considering the limitation of the architecture in storing binary version of floating point numbers. Money is a standard unit and will surely have more support for handling money related operations. In case of decimal, you'll have to handle each and everything but you know it's only you who is handling a decimal type, thus no surprises which you may get with other two data types.

Amber
  • 290
  • 1
  • 3
  • 10
1

For some data (like money) where you want no approximation or changes due to float value, you must be sure that the data is never 'floating', it must be rigid on both sides of decimal point.
One easy way to be safe is, to value by converting it into INTEGER data type, and be sure that while you retrive the value, decimal point is placed at proper location.
e.g.
1. To save $240.10 into database.
2. Convert it to a pure integral form: 24010 (you know its just the shift of decimal).
3. Revert it back to proper decimal state. Place decimal at 2 positions from right. $240.10

So, while being in databse it will be in a rigid integer form.

Satyendra
  • 1,635
  • 3
  • 19
  • 33
1

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
  • 94,695
  • 15
  • 113
  • 186