How to format % and in 2 decimal points?
How do I code format the return data in 2 decimals and with percentage format like 100.00% or 67.39% instead of 100.000000 or 67.391304?
SUM(qa.scripting1+qa.conduct1+qa.conduct2+qa.conduct3)*100.0/46 as 'C%'
ROUND() but I got the error stating that the round function requires 2 to 3 arguments?
ROUND(SUM(qa.scripting1+qa.conduct1+qa.conduct2+qa.conduct3)*100.0/46) as 'C%'
4 Solutions collect form web for “How to format % and in 2 decimal points?”
You can convert to a decimal your original value:
CONVERT(VARCHAR(20), CONVERT(DECIMAL(18,2), SUM(qa.scripting1+qa.conduct1+qa.conduct2+qa.conduct3)*100.0/46) ) + '%' as 'C%'
The first number in the decimal represents the number of digits in the number including decimal places, and the second number represents the number of decimal places.
You should pass number of decimals in second parameter to round function. For formating you can cast number to money and then cast to varchar:
select cast(cast(ROUND(SUM(123.12321)*100.0/46, 2) as money) as varchar) + '%'
Using Round and Cast will work. First round to 2 decimal places then convert to a decimal with 2 places to truncate the excess zeros.
select cast(Round(yourValue, 2) as decimal(18,2))
You can use Format function
select FORMAT(100.0000, 'N' , 'en-us')
select FORMAT(67.391304, 'N' , 'en-us')
In version below 2012 you can do this
SELECT CAST(67.391304 AS NUMERIC(10, 2))