Format number as percent in MS SQL Server
I am trying to simply format a number as a percent with two decimal places. If it is 37 divided by 38 (aka .973684210526315789), I would like it to show 97.36 % in the SQL output. I know it is recommended to do formatting in the Application, however this is for an automated export. This is using SQL Server 2008.
Here is what I have now:
select CONVERT(VARCHAR(50),cast(37 as decimal)/cast(38 as decimal)*100)+' %' AS [%]
If you could explain what the various parameters are as well in any function that would be helpful.
M.Ali's answer could be modified as
select Cast(Cast((37.0/38.0)*100 as decimal(18,2)) as varchar(5)) + ' %' as Percentage
In SQL Server 2012 and later, there is the FORMAT() function. You can pass it a 'p' parameter for percentage. For example:
SELECT FORMAT((37.0/38.0),'p') as [Percentage]
SELECT cast( cast(round(37.0/38.0,2) AS DECIMAL(18,2)) as varchar(100)) + ' %' RESULT: 0.97 %