Need to convert decimal places to 2 in SQL statement

[MaffewW]

Hi all

I am doing some calculations on a varchar field and need to get the result to 2 decimal places

So far I have:

select sum(convert(decimal,propertyvalue)/3600) from table1

any ideas?

Cheers

 

[sagn]

So the calCULATION is on a varchar field?

Anyway have you tried to convert to a real and then
round it?

syntax = ROUND(number,2)

 

[williamu]

select round(sum(convert(decimal, @this) / 3600), 2) ;

Sould do what you want but it could be argued that you've started with the wrong data type if you need to do calculations.


HTH.
William
Software Engineer
ICQ No. 56047340

 

[MaffewW]

Thanks for the replys

What I am actually trying to do is not to round the number up, but to get it to 2 d.p

ie select round(1.55333,2) will give 1.55000 and I need it to give 1.55 - I cant trim it because after the calculation not all the numbers are the same length.

The database is part of our automatic phone system and I cant change the datatype to something more appropriate as theres non numerical values in the field

 

[sagn]

Use this to get what you want

str(@i,@j,@k) where @i is a floating point number
@j is the length of the resulting str and @k is the number of digits beyond
the decimal you want...

And ROUND does not round up but *ROUNDS*
either up or down depending on the value of the digit next to the last one you want...

 

[MaffewW]

Sagn, that worked a treat. Though I had to trim the floating point number as it didnt work on reccuring numbers

Thanks for your help

 

[tlbroadbent]

How about using the precision and scale options when converting the varchar to decimal?

select
sum(convert(decimal(8,2),propertyvalue)/3600.)
from table1

You can also use CAST.

Cast(Propertyvalue AS Decimal(8,2)/3600. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.