SQL Server MONEY datatype limitations11 juni 2015 2015-06-11 12:58
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):
SELECT [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.