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 datatypecan accept up to four scale values.

<span>DECLARE </span><span>@money </span><span>money<br /> </span><span>SET </span><span>@money</span><span>=</span><span>'$1,200.45679'<br /> </span><span>SELECT </span><span>@money<br /> </span>

which results to 1200.4567

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

<span>DECLARE </span><span>@money </span><span>money<br /> </span><span>SET </span><span>@money</span><span>=</span><span>1200.45679<br /> </span><span>SELECT </span><span>@money</span>

which results to 1200.4568

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

<span>DECLARE </span><span>@money </span><span>money<br /> </span><span>SET </span><span>@money</span><span>=</span><span>'1,200.45679'<br /> </span><span>SELECT </span><span>@money</span>

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

<span>DECLARE </span><span>@money </span><span>FLOAT<br /> SET </span><span>@money</span><span>=</span><span>'1,200.45679'<br /> </span><span>SELECT </span><span>@money</span>

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

<span>DECLARE </span><span>@money </span><span>money<br /> </span><span>SET </span><span>@money</span><span>=</span><span>'$1,200.45679'<br /> </span><span>SELECT </span><span>@money</span>

Observation 6:All commas are omitted in Money datatype

<span>DECLARE </span><span>@money </span><span>money<br /> </span><span>SET </span><span>@money</span><span>=</span><span>'1,2,0,0.4,5,6,7,9'<br /> </span><span>SELECT </span><span>@money</span>

which results to 1200.4568

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