April 4, 2010 at 2:10 pm
Hi
Can someone explain what is money datatype..
select CONVERT(money,'1,0,0,0,0,0,0.0,0,0,1')
Regards
VMSSanthosh
April 4, 2010 at 2:22 pm
Heh... "fuzzy, forgiving input" that disregards just about all currency marks other than the decimal point except when set to the European format where periods represent commas and a comma is the decimal point.
Look at it this way... "It's not a fault, it's a feature." 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 4, 2010 at 2:25 pm
September 19, 2016 at 3:57 am
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:[/b] 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
Thanks !!!
September 19, 2016 at 4:23 am
Other noteworthy remarks:
when mixing the money datatype with other datatypes, precision issues can occur.
However, using the money data type can lead to faster SSAS processing performance.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 20, 2016 at 12:29 pm
The money datatypes were created by Sybase in the original SQL server for COBOL programmers. In COBOL, they have a PICTURE clause in their file declarations, which hold display formatting such as commas, currency signs, and decimal points.
Unlike SQL, COBOL is monolithic. The display formatting is done in the same program as the data and computations. But in SQL, we have a tiered architecture for data is passed to a presentation layer that should be doing this.
The money datatype also has arithmetic problems. It does the rounding at the wrong time and computations. This does not happen with DECIMAL(S,P) data.
http://blog.learningtree.com/en/is-money-bad-the-money-datatype-in-sql-server/
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply