Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Money Expand / Collapse
Author
Message
Posted Sunday, April 4, 2010 2:10 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 8, 2016 6:46 AM
Points: 126, Visits: 167

Hi

Can someone explain what is money datatype..

select CONVERT(money,'1,0,0,0,0,0,0.0,0,0,1')


Regards
VMSSanthosh
Post #896468
Posted Sunday, April 4, 2010 2:22 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:08 PM
Points: 42,077, Visits: 39,460
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."

Helpful Links:
How to post code problems
How to post performance problems
Post #896470
Posted Sunday, April 4, 2010 2:25 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 6:42 AM
Points: 9,932, Visits: 11,347
See:

Using Monetary Data
Money and SmallMoney

Example:
SELECT $123.67





Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Post #896471
Posted Monday, September 19, 2016 3:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 21, 2016 6:26 AM
Points: 8, Visits: 15
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][/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 !!!
Post #1818773
Posted Monday, September 19, 2016 4:23 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 2:09 AM
Points: 15,501, Visits: 13,163
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1818782
Posted Tuesday, September 20, 2016 12:29 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:21 AM
Points: 1,945, Visits: 4,239
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
Post #1819308
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse