Home > SQL Server > Money vs Decimal in Sql Server

Money vs Decimal in Sql Server

I normally use the Money data type when designing databases , as I’ve always read that it was specifically designed for currencies. One of the advantages , for example , is that you can use a currency symbol with it. Recently I came accross something very interesting while browsing the net. Apparently there are accuracy issues with the data type. I have below a simple example to demonstrate. Note that this isn’t my example but something that I found on the web which I thought I’d share :

declare @m money
declare @d decimal(9,2)

set @m = 19.34
set @d = 19.34

select (@m/1000)*1000
select (@d/1000)*1000

So what would the results be ? Well if you’re expecting 19.34 for the money variable , you’d be wrong !! You actually get 19.30. Yup, I was surprised as you are right now. I’ve even tested this in the Katmai CTP and it does the same thing. So from now on I’ll try to use decimal and specify the precision I need , when creating tables. Any feedback on why you’d want to still use Money ? (I’m sure that there’s a reason )

UPDATE : HussainSafal sent in a little tip – if you add a decimal point to the numbers in the operation ( in this case the denominator ) , the operation will produce the correct result when using a variable of type money. If that made no sense – then look at this :

select (@m/1000.0)*1000.0

Categories: SQL Server
  1. Unknown
    March 31, 2010 at 9:02 pm

    thanks for noting this. this is not cool.

  2. san
    March 15, 2011 at 11:11 am

    Thanx a lot.

  3. March 15, 2011 at 11:32 am

    Thanx, but i ran the following:

    select (@m/1000.0000)*1000.0000 as money1, (@m/1000)*1000 as money2,(@m/1000.0)*1000.0 as money3
    select (@d/1000.0000)*1000.0000 as decimal1, (@d/1000)*1000 as decimal2

    do u think that this would solve the accuracy problem.

  4. March 16, 2011 at 8:22 am

    @hussainsafal – you’re right – it does seem to solve the problem. Thanks for the tip !!

  5. Denis
    January 22, 2015 at 11:24 am

    You truncate the value yourself and then assign the truncated value to your result.

    If you change your calculation to
    set @m = (19.34/1000)*1000
    select @m

    you will see that the value is correct.

    It really depends on how you want to calculate the result. Money

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: