SQL SERVER – Interesting Observations Using MONEY Datatype

MONEY is one of the data types supported in SQL Server. There are some interesting information about this. Here are a few observations about the MONEY datatype.

Observation 1: Money datatype can accept up to four scale values.

DECLARE @money money
SET @money='$1,200.45679'
SELECT @money

which results to 1200.4567

Observation 2: If the scales value exceeds 4 digits, it is rounded to four digits

DECLARE @money money
SET @money=1200.45679
SELECT @money

which results to 1200.4568

Observation 3: Money datatype is the only datatype that can accept formatted numbers

DECLARE @money money
SET @money='1,200.45679'
SELECT @money

Observation 4: If you use decimal , numeric or float you will get an error

DECLARE @money FLOAT
SET @money='1,200.45679'
SELECT @money

The error is

Msg 8114, Level 16, State 5, Line 3
Error converting data type varchar to float.

Observation 5: Money datatype can also accept currency symbols prefixed with a number

DECLARE @money money
SET @money='$1,200.45679'
SELECT @money

Observation 6: All commas are omitted in Money datatype

DECLARE @money money
SET @money='1,2,0,0.4,5,6,7,9'
SELECT @money

which results to 1200.4568

When I Use the MONEY Datatype and When I Do Not

MONEY takes 8 bytes and always keeps four digits after the decimal point. Its smaller cousin SMALLMONEY takes 4 bytes and has a much smaller range. For simply storing prices and amounts, both work fine, and they are accurate to one ten thousandth of a currency unit.

The trouble starts with calculations. Because MONEY keeps only four decimal places, the result of a division is rounded early. For example, dividing a MONEY value of 1 by 3 gives 0.3333, and multiplying that by 3 gives 0.9999, not 1. In invoices with tax, discounts and splits, these small differences add up, and someone in finance will notice. For that reason, I prefer DECIMAL(19,4) for columns that go through a lot of math, with a higher scale for the intermediate results.

Also remember that a currency symbol is accepted on input but not stored. The column holds only the number, so if your application deals with more than one currency, keep the currency code in its own column.

And when you need the commas back for a report, CONVERT(VARCHAR(30), @amount, 1) formats a MONEY value with commas and two decimal places.

If you already have MONEY columns, there is no need to panic or rebuild anything. Storing the values is fine. Just cast to DECIMAL with enough scale before you divide or multiply, for example CAST(Amount AS DECIMAL(19,4)) / 3, and round once at the very end.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.