Throw Your MONEY Away

This might not be a big revelation to many of you, but do you see any glaring similarities in the following datatypes?:

/*
DataType              Range (Smallest to Largest)                   #Bytes
--------------------------------------------------------------------------
integer                -2,147,483,648 to 2,147,483,647                   4
smallmoney              -214,748.3648 to 214,748.3647                    4
--------------------------------------------------------------------------
bigint     -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807       8
money       -922,337,203,685,477.5808 to 922,337,203,685,477.5807        8
--------------------------------------------------------------------------
*/

That’s right… the money datatypes are really just integers in disguise.

And, unfortunately, these money datatypes carry the baggage of integer division with them.

Consider the following:

declare @m money
set @m=500
select DivBy1600=@m/1600
      ,DivBy16000=@m/16000
      ,DivBy160000=@m/160000
      ,DivBy1600000=@m/1600000
      ,DivBy16000000=@m/16000000
/*
    DivBy1600    DivBy16000   DivBy160000  DivBy1600000 DivBy16000000
------------- ------------- ------------- ------------- -------------
       0.3125        0.0312        0.0031        0.0003          0.00
*/

You can see that when 500 is divided by 16,000, the result is truncated (not rounded) to 4 decimals… the result is 0.0312 and not 0.0313.

And that truncation of 4 decimal places can produce some unexpected results:

declare @m money
set @m=500
select By1600=@m/1600*1600
      ,By16000=@m/16000*16000
      ,By160000=@m/160000*160000
      ,By1600000=@m/1600000*1600000
      ,By16000000=@m/16000000*16000000
/*
      By1600      By16000     By160000    By1600000   By16000000
------------ ------------ ------------ ------------ ------------
      500.00       499.20       496.00       480.00         0.00
*/

One would expect to come up with an answer of 500 for each of those… But nooooo.

Notice that the same phenomenon does not happen when doing the same operations on a decimal (or numeric) datatype:

declare @d decimal(5,2)
set @d=500
select DivBy1600=@d/1600
      ,DivBy16000=@d/16000
      ,DivBy160000=@d/160000
      ,DivBy1600000=@d/1600000
      ,DivBy16000000=@d/16000000
/*
    DivBy1600    DivBy16000   DivBy160000  DivBy1600000 DivBy16000000
------------- ------------- ------------- ------------- -------------
    0.3125000    0.03125000   0.003125000  0.0003125000 0.00003125000
*/
select By1600=@d/1600*1600
      ,By16000=@d/16000*16000
      ,By160000=@d/160000*160000
      ,By1600000=@d/1600000*1600000
      ,By16000000=@d/16000000*16000000
/*
           By1600          By16000        By160000       By1600000      By16000000
----------------- ---------------- --------------- --------------- ---------------
      500.0000000     500.00000000   500.000000000  500.0000000000 500.00000000000
*/

Here’s another money tidbit that may cause confusion among new users of T-SQL…

According to Books Online, you represent a money constant as a string of numbers with an optional currency symbol as a prefix. So you can represent a money value of 100 as $100 or £100 or ¥100 or €100, for example. But of course this doesn’t take into account any conversion ratios, as the following demonstrates:

select Revelation=
         case 
           when $100=£100 and $100=¥100 and $100=€100 
           then 'Really?  Dollars and Pounds and Yen and Euros are all equal??'
           else 'Of course they''re different... Who are you kidding?'
         end
/*
Revelation
-------------------------------------------------------------
Really?  Dollars and Pounds and Yen and Euros are all equal??
*/

There is one interesting thing (though laughably trivial) about using the money datatype. T-SQL will allow you to convert strings with commas into money, but it will not allow you to convert them into decimal or numeric:

declare @m money, @d decimal(10,2)
set @m='1,234,567.89'  /* This works fine */
set @d='1,234,567.89'  /* Msg 8114: Error converting data type varchar to numeric */

Whoop-de-doo.

In reality, the commas are simply removed before the conversion… the actual quantity or placement of the commas is not validated in any way. For example, the following executes without error:

declare @m money
set @m=',1,,2,3,,,4,5,6,7,,,.8,9,,'
select @m  /* Returns 1234567.89 */

I’ve personally never used the money datatype, and I certainly never will. As the biblical saying goes: “For the love of money is the root of all inaccurate calculations and currency confusion… oh yeah, and evil too.”