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

I tried ROUND() but I got the error stating that the round function requires 2 to 3 arguments?

  • Is there a difference between (local), '.' and localhost?
  • SQL Server Pivot or Unpivot
  • T-SQL select if person has product A but not both A and B
  • SQL job result in c#
  • jdbc:jtds Connection to SQL server
  • 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))

    Sql Fiddle

    You can use Format function

    select FORMAT(100.0000, 'N' , 'en-us')

    returns 100.00


    select FORMAT(67.391304, 'N' , 'en-us')

    returns 67.39


    In version below 2012 you can do this

     SELECT CAST(67.391304 AS NUMERIC(10, 2))

    returns 67.39

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.