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.
This question is related to
sql-server
tsql
percentage
took the challenge to solve this issue. comment number 5 (M.Ali) asked for --"The required answer is 97.36 % (not 0.97 %) .." so for that i'm attaching a photo who compare between post number 3 (sqluser) to my solution. (i'm using the postgre_sql on a mac)
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] -- 97.37 %
To support percentage decimal precision, you can use P0
for no decimals (whole-numbers) or P3
for 3 decimals (97.368%).
SELECT FORMAT((37.0/38.0),'P0') as [WholeNumberPercentage] -- 97 %
SELECT FORMAT((37.0/38.0),'P3') as [ThreeDecimalsPercentage] -- 97.368 %
And for all SQL Server versions
SELECT CAST(0.973684210526315789 * 100 AS DECIMAL(18, 2))
SELECT cast( cast(round(37.0/38.0,2) AS DECIMAL(18,2)) as varchar(100)) + ' %'
RESULT: 0.97 %
Using FORMAT function in new versions of SQL Server is much simpler and allows much more control:
FORMAT(yournumber, '#,##0.0%')
Benefit of this is you can control additional things like thousand separators and you don't get that space between the number and '%'.
Source: Stackoverflow.com