What data-type should I use to store monetary values?

Question

I've read I should use money, but in todays fast paced world maybe that's obsolete now.

What should I use?

Answer 1

No, money should still work.

Answer 2

Yes, the money datatype is quite obsolete. It's been superseded by the creditcard datatype :-)

Answer 3

Why should money be obsolete? It goes up to over 900 trillions, hundreds of times the Federal Government's budget -- what kind of money amounts do you possibly need to store?-) (I guess that maybe Zimbabwe dollars might eventually have been a problem, but they kept resetting it by multiples of billions and trillions, and last April it was finally suspended; they use US dollars or other foreign currencies now for payments & accounting in Zimbabwe).

Answer 4

Papuccino,

I don't recommend the types money and smallmoney unless you are certain the only arithmetic you plan to do is addition and subtraction. If you might be dealing with exchange rates, percentages, and so on, you risk real problems with these types.

Here's just one small example to show you a difference between using money, decimal, and float when division is involved. It's possible to come up with examples where the difference is much more dramatic.

declare @m1 money, @m2 money, @m3 money
declare @d1 decimal(19,4), @d2 decimal(19,4), @d3 decimal(19,4)
declare @f1 float, @f2 float, @f3 float;
set @m1 = 1.00;
set @m2 = 345.00;
set @m3 = @m1/@m2;
set @d1 = 1.00;
set @d2 = 345.00;
set @d3 = @d1/@d2;
set @f1 = 1.00;
set @f2 = 345.00;
set @f3 = @f1/@f2;
select @m3, @d3, @f3;

Result: 0.0028 0.0029 0.00289855072463768

Depending on the industry, there may be guidelines or regulations to help you decide on the right data type. There is no one right answer.

Added remarks:

You are correct that money/money should not be money, but SQL Server (inexplicably) produces exactly that result: type money from the quotient of two money values. This is bogus, but as you see from the example below, it is what you get, even though it makes no sense:

declare @m1 money, @m2 money;
declare @d1 decimal(19,4), @d2 decimal(19,4);
set @m1 = 1.00;
set @m2 = 345.00;
set @d1 = 1.00;
set @d2 = 345.00;
select @m1/@m2, @d1/@d2

Result: 0.0028 0.0028985507246376811

The result with type money, 0.0028, is 3-4% less than the correct result.

Of course, there are many situations where you need to divide currency values. The danger of using the money type is that the quotient is the wrong type (and an answer not close enough to the correct one). Examples of questions that require dividing currency:

Suppose you exchange 320 Yuan and the bank gives you 47.3 US dollars. What is the exchange rate you've been given?

Suppose you invest $23 and a year later it's worth $31. What is your percent rate of return?

Both of these calculations require dividing currency values.

Answer 5

I agree money divided by money is bogus. But money divided by days is real. If you are dividing small amounts of money by the number of days you need to spead the cost, it is most important to keep watch of this phenomenon. I cast/convert money to float, do computation before eventually storing the end result to money data type. Hope this helps.