Rounding in SQL 1

[Trudye]

Hi SQL'ers, I cannot get the ROUND function to work in Query Analyzer. It is still showing 4 decimal places.

select title, round(price,2) as MyPrice, advance, type
from Titles
where (price > 15.00 or advance > 5000.00) and type = 'business'

How can I show only 2 decimal places, price is defined as money in SQL2000.

Thanks much
Trudye

 

[amorous]

Try this:

select title, CAST(price as DECIMAL(6,2)) as MyPrice, advance, type
from Titles
where (price > 15.00 or advance > 5000.00) and type = 'business'

-VJ

 

[Trudye]

COOL!

Trudye

 

[amorous]

I am glad that it worked for you.

I suppose that you understood what i meant from
DECIMAL(6,2) -- 2 places to the right of decimal and 6 places to the left of the decimal.

if you have a value like 1234567.5677, the above code does not work and you have to make neccesary changes.

-VJ

 

[Trudye]

I changed Price and it worked fine, but when I changed Advance I got the following error. Where-oh-Where did I go wrong?

(2 row(s) affected)

Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.

select title, cast(price as DECIMAL(6,2)) as MyPrice,
cast(advance as DECIMAL(6,2)) AS MyAdvance, type from Titles
where (price > 15.00 or advance > 5000.00) and type = 'business'

 

[amorous]

Yep, i told you...

change your code to following..


select title, cast(price as DECIMAL(10,2)) as MyPrice,
cast(advance as DECIMAL(10,2)) AS MyAdvance, type from Titles
where (price > 15.00 or advance > 5000.00) and type = 'business'

VJ

 

[Trudye]

Got it Vj. You are correct oh wise one, my Advance field values are larger than my Price field values. Works
pefect-o now.

Thanks for hanging in there with me.

TEN

 

[amorous]

Thanks for the star.

-VJ