Is Your MONEY Safe in SQL Server

In this post i would like to share my experience with you on MONEY data type in SQL Server .


Let’s looks at one example where MONEY data type will round numbers.

Usage of MONEY data type in storing amounts

DECLARE @Money_Amt1 MONEY, @Money_Amt2 MONEY

SET @Money_Amt1 = 100100.1234567;
SET @Money_Amt2 = 100100.1234;

SELECT @Money_Amt1 AS Money_Amt1,
@Money_Amt2 AS Money_Amt2;

As you can see Money data type can hold only up to 4 digits after the decimal. If you are trying to assign a number whose scale is more than 4 digits, then money data type rounds the number, in which case over the course of the time you will accumulate lot of in correct amounts. So you need to make right decision in choosing the right data type for your data.


FYI: This is not a bug neither this is the limitation.


Let’s look at one example where you want to multiply two MONEY data type variables .

Usage of MONEY data type in Multiplication

DECLARE @Your_Money MONEY, @Currency_Conversion_Rate MONEY;

SET @Your_Money = 12.2345;
SET @Currency_Conversion_Rate = 18.7686;

SELECT (@Your_Money * @Currency_Conversion_Rate) AS Col1,
(@Your_Money * 1.0 * @Currency_Conversion_Rate) AS Col2;


As you can see in the above query, I declared two variables. The result of the multiplication or division of two money data type is always money. As discussed previously money data type can hold only 4 digits after the decimal and if it is anything more than that it tries to round to the nearest number. As you can see in the Col1, you don’t see the accurate results of the multiplication operation and whereas in the Col2 by multiplying with 1.0, basically SQL Server is converting into numeric.