Money

Question

Money

vmssanthosh

SSC Eights!

Points: 856
More actions
April 4, 2010 at 2:10 pm

#386642

Hi
Can someone explain what is money datatype..
select CONVERT(money,'1,0,0,0,0,0,0.0,0,0,1')
Regards
VMSSanthosh

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 2

See:

Using Monetary Data

Money and SmallMoney

Example:

SELECT $123.67

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Kalegley Old Hand Points: 328 More actions · Answer 3

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 !!!

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

Other noteworthy remarks:

when mixing the money datatype with other datatypes, precision issues can occur.

http://stackoverflow.com/questions/582797/should-you-choose-the-money-or-decimalx-y-datatypes-in-sql-server

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

Joe Celko SSCertifiable Points: 5749 More actions · Answer 5

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