SQL Server MONEY datatype limitations

Be very, VERY careful when using SQL Server's MONEY datatype.

It's okay for storage of money things (offers some nice features too), and has an accuracy of 0.0001.

Once you start calculating with that, beware. 1/10000th is quite a limitation.

Just execute this small script to see possible results (real-world case):

  [regularType] = (7821.97 / 549822.00) * 69248.00
  , [MoneyType] = (CAST(7821.97 AS MONEY) / CAST(549822.00 AS MONEY)) * CAST(69248.00 AS MONEY)

The first column contains 985.1475174..., close enough for the financial report I had to deliver. The second column was the result I got returned because of using the MONEY-datatype in a UDF performing these calculations. The result is 983.3216, which led me at first to check all other calculations because of the deviation of 1.82.

Founder of this blog. Business Intelligence consultant, developer, coach, trainer and speaker at events. Currently working at Dura Vermeer. Loves to explain things, providing insight in complex issues. Watches the ongoing development of the Microsoft Business Intelligence stack closely. Keeping an eye on Big Data, Data Science and IoT.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Next ArticleIf you ever need to determine daylight saving time in Europe, here's the T-SQL script