Money

  • Hi

    Can someone explain what is money datatype..

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

    Regards

    VMSSanthosh

  • 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)

  • See:

    Using Monetary Data

    Money and SmallMoney

    Example:

    SELECT $123.67

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

  • 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

  • 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