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)

,
Previous Post
SQL SERVER – Database Size Limitation in SQL Express
Next Post
SQL SERVER – How to Identify InMemory Objects Can be Identified in SQL Server?

Related Posts

2 Comments. Leave new

  • Ian Yates (@IanYates82)
    October 23, 2015 12:51 pm

    Nice to know :) Just a quick note – your first example has the rounding wrong (you show it correctly in the second one). I get 1200.4568, not 1200.4567.

    Reply
  • MONEY was added to Sybase to keep the old COBOL programmers happy. It replaced the PICTURE clause, so that T-SQL could be used to write monolithic modules of code, instead of putting the code into database and presentation layers.

    The other problems have to do GAAP and EU rules for currency, which it does not do correctly.

    Reply

Leave a Reply

Menu