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.

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

Reference: Pinal Dave (https://blog.sqlauthority.com)