- Home /

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.

Comment

Tom Staab

Best Answer

**Answer** by Tom Staab
·
Nov 13, 2015 at 06:26 AM

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)) + '%'

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."

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.

Hi Tom, Thanks, it works, however, I am getting 0.000000000000%. I could not shrink it down to 1 or 2 decimals unfortunately.

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.

That is perfect. Thank you very much Tom!! Very helpful!

Copyright 2019 Redgate Software. Privacy Policy

- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Ask SSC Site Issues (meta-askssc)
- Explore
- Topics
- Questions
- Users
- Badges