question

Infock12 avatar image
Infock12 asked

Unable to convert percentage into decimals

Any help is appreciated. I would like the values to show 0.0%. I tried all the following methods: ,STR(ROUND(([Future]*100.0/Total), 2) as decimal(5,1)) + '%' as 'Percentage' Incorrect syntax near the keyword 'AS' cast(ROUND(([Future]*100.0/Total), 2) as decimal(5,1)) + '%' AS 'Percentage' Error converting data type varchar to numeric. ,CAST(ROUND(([Future]*100.0/Total), 2) as decimal(5,1)) AS 'Percentage' This gives me the desired result (0.0), but without the % symbol. ,str(ROUND(([Future]*100.0/Total), 2)) + '%' AS ‘Percentage’ This works but without the decimals (0%) Not sure what am I doing wrong.
sql-server-2012convertdecimalcast-convert
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Tom Staab avatar image
Tom Staab answered
You need to do the math, then convert to string, and then add the percentage symbol. Also, I'm confused why you are rounding to 2 decimal places if you want to only show 1. Also, by default, the engine will round when converting to a decimal type with a smaller scale. That said, I'll give an example using ROUND but only converting to string. Try this: CONVERT(varchar(40), CONVERT(decimal(38, 2), Future*100.0/Total)) + '%'
5 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Infock12 avatar image Infock12 commented ·
Hi Tom, Thanks for the reply. I tried to convert it into a decimal, doesn't matter whether 1 or 2. I tried your version but it gives me an error message "Arithmetic overflow error converting numeric to data type varchar."
0 Likes 0 ·
Tom Staab avatar image Tom Staab ♦ commented ·
I used varchar(10) only because you had decimal(5,1), so I assume 10 characters was more than enough. Try varchar(20) to see if that works. I'll update my answer.
0 Likes 0 ·
Infock12 avatar image Infock12 commented ·
Hi Tom, Thanks, it works, however, I am getting 0.000000000000%. I could not shrink it down to 1 or 2 decimals unfortunately.
0 Likes 0 ·
Tom Staab avatar image Tom Staab ♦ commented ·
Okay. I can solve that for you by using CONVERT instead of ROUND. Just set the precision and scale large enough to avoid any overflow.
0 Likes 0 ·
Infock12 avatar image Infock12 commented ·
That is perfect. Thank you very much Tom!! Very helpful!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.