decimal places on money cast

  • Hi

    I have an SQL search that is converting two values to type money.  I want it to show two digits after the decimal point but am getting inconsistent results.  The first value is as follows:

    cast(tblInventoryItem.itemcost as money) as originalcost

    This displays correctly i.e. 2000.00 or 150.70 etc

    The second value is this:

    cast (tblInventoryItem.itemcost + tblUpgrades.ItemCost as money)) as totalcostincupgr

    But this displays as 2000 or 150.7

    How can I get the second value to show two decimal places even when the digits are zeros?

    Thanks!

  • As always, formatting results is best left to the front-end application if possible. SQL Server is a RDBMS system for transactions and formatting options are limited.

    However, for the second value if you CAST both columns as money before combining them, this should give you the correct result.

    Out of interest what datatype are the two columns?



    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

  • Hi Adrian

    They're both datatype money anyway, so maybe I don't need to do the cast at all.  I did try taking that out of the sql statment and just had:

    tblInventoryItem.itemcost as originalcost

    tblInventoryItem.itemcost + tblUpgrades.ItemCost as totalcostincupgr

    but the totalcostincupgr is still not showing the digits after the decimal point correctly.

    Any ideas?

  • Please define in what program you are running the query.

    "Showing" is the application's decision, try using Query Analyzer and Results to Text output, you should always see 4 decimal places for a money data type.

    Generally the left most parameter in a calculation determines the resulting data type.

    Andy

     

  • I have noticed similar results so I cast as numeric and round to ensure that my data consistent. If you are using larger numbers you might increase the 8 tp 12 or something.

    cast(round(tblInventoryItem.itemcost + tblUpgrades.ItemCost,2) as numeric(8,2)) as totalcostincupgr

    Mike

  • Jude,

    What are you actually using?  Using CAST(anynumber AS MONEY) always results in 4 decimal places, not the 2 that you claimed it does in your first example...

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Hi All

    thanks for your replies.  I've actually done the formatting in my code now as what I was putting into the SQL query was not affecting what showed up on the screen.

    Thanks.

  • Thanks mike

     

    this code help me...

     

    cast(round(tblInventoryItem.itemcost + tblUpgrades.ItemCost,2) as numeric(8,2)) as totalcostincupgr

     

     

     

Viewing 8 posts - 1 through 8 (of 8 total)

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