decimal places on money cast

Question

decimal places on money cast

Jude Lynn

SSC Veteran

Points: 279
More actions
September 6, 2007 at 2:13 am

#180940

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!

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

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

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 1

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]

Jude Lynn SSC Veteran Points: 279 More actions · Answer 2

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?

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 3

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

Mike Perkins-458755 SSC Rookie Points: 43 More actions · Answer 4

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

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

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.

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

Jude Lynn SSC Veteran Points: 279 More actions · Answer 6

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.

jay-310822 SSC Journeyman Points: 78 More actions · Answer 7

Thanks mike

this code help me...

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