SQLTeam.com | Weblogs | Forums

Convert Varchar round value to 2 decimals

sql2012

#1
HI 

I am trying to get the desired result with two decimal TEST (70.00%) but below query showing me
TEST (70.000000%) 5 decimal , tried to round the values but not works

Select 
    Case when field = 'TEST'  then 'TEST'	+ ' (' + convert(varchar, shares.[PPL Shares], 1) +'%) 'ELSE '' END
    from Table 

Result = TEST (70.000000%)

Select 
    Case when field = 'TEST'  then 'TEST'	+ ' (' + convert(varchar, round(shares.[PPL Shares], 1),2) +'%)' ELSE '' END
    from Table 

Result = TEST (70.000000%)


#2

Ideally, you should do the formatting in the presentation layer. If you were using C#, for example, you can format as decimal with two decimal points using the standard format string "P2". The column value is multiplied by 100 and presented as a percentage.

You can format in SQL Server as well - like shown below (the result might appear to have been multiplied by 100, in which case you should divide the column by 100 before formatting.

SELECT CASE WHEN field = 'TEST' THEN
                'TEST' + ' (' + FORMAT(shares.[PPL Shares], 'P2') + ')'
            ELSE ''
       END
FROM   Table;

#3

Lordy, don't use FORMAT. It's 44 times slower than convert and a bazillion times slower than datatype conversions.


#4

Although ... "it depends" ...

... but I agree that in 99.9999% of questions asked here its the wrong solution. Trouble is, the questions lack all context, so never possible to know if it fits one of the "it depends" scenarios ...

Need Graz to write and sp_depends for forum questions ...


#5

Not in this case. The performance hit of FORMAT just isn't worth it. Work arounds are easy to accomplish.