Why not to use a MONEY data type to represent a currency or other financial value

Remember my last post ‘Why not to use a float to represent a currency or other financial value‘?

Here’s another one in regards to the usage of the MONEY data type:


--Decimal/Numeric versus Money:

DECLARE
@mon1 MONEY,@mon2 MONEY,@mon3 MONEY,@mon4 MONEY,
@num1 DECIMAL(19,4),@num2 DECIMAL(19,4),@num3 DECIMAL(19,4),@num4 DECIMAL(19,4),@num5 DECIMAL(19,4)

SELECT
@mon1 = 100, @mon2 = 339, @mon3 = 10000,
@num1 = 100, @num2 = 339, @num3 = 10000

SET @mon4 = @mon1/@mon2*@mon3
SET @num4 = @num1/@num2*@num3

SET @num5 = @num1/@num2

SET @num5 = @num5*@num3

SELECT @mon4 AS moneyresult,
@num4 AS numericresult,
@num5 AS numericresult2

The result:

money-data-type-result



Download the complete example:
Decimal-Numeric versus Float and Money








Advertisements

6 thoughts on “Why not to use a MONEY data type to represent a currency or other financial value

  1. It seems many people do not understand where temporary variables are stored and how they are typed. In your example, the result of the operation @num1/@num2 is a FLOAT. It is NOT a DECIMAL(19,4). Intrinsic types are used as TEMPORARY variables (i.e. storage for intermediate operations) by the processor. There is no magic here–it is a function of the Intel CPU architecture and NOT a function of SQL Server. Your article does a good job illustrating why one should use intermediate variables that are strongly typed if one wishes to control behavior. Under the covers, SQL Server will use TYPED intermediate variables when working with MONEY types…this is so that the behavior you illustrate does not happen.

    • Tony thanks for your comment. For me that’s a bit too technical, but I wanted to demonstrate (for me and perhaps other people) the ‘strange’ behavior when working with MONEY of FLOAT types.

  2. This is a good example of the behavior. Money is not a FLOAT type…it is the only correct data type to use. FLOATs approximate a value, MONEY is the actual value. Your post also shows why you should TYPE intermediate financial calculations. Good job.

  3. illustrated with code:

    DECLARE
    @mon1 MONEY,@mon2 MONEY,@mon3 MONEY,@mon4 MONEY,
    @num1 DECIMAL(19,4),@num2 DECIMAL(19,4),@num3 DECIMAL(19,4),@num4 DECIMAL(19,4),@num5 DECIMAL(19,4)

    SELECT
    @mon1 = 100, @mon2 = 339, @mon3 = 10000,
    @num1 = 100, @num2 = 339, @num3 = 10000

    SET @mon4 = convert(DECIMAL(19,4),@mon1)/convert(DECIMAL(19,4),@mon2)*convert(DECIMAL(19,4),@mon3)
    SET @num4 = @num1/@num2*@num3

    SET @num5 = @num1/@num2

    SET @num5 = @num5*@num3

    SELECT @mon4 AS moneyresult,
    @num4 AS numericresult,
    @num5 AS numericresult2

  4. The only correct answer, financially, is the answer from the Money data types. Using the other types at a financial institution might be fraud and would certainly get you fired.

  5. I recently did a job which required me to read JD Edwards data and convert it to T-SQL. I was surprised that the data was scaled decimal which is handled as integer and rounded to the nearest integer value by the software. The internal representation is a big integer within a decimal data type scaled by 100. It is displayed and printed correctly, descaled in the output functions.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s