Format a Percentage

Question

Post reply

Format a Percentage

Welsh Corgi

SSC Guru

Points: 116520
More actions
December 29, 2011 at 5:55 am

#262234

I need to alter the following code:
DECLARE @SalesDECIMAL (18,2)
DECLARE @CommissionDECIMAL (18,2)
DECLARE @CommissionRateVARCHAR(200)
SET @Sales= '1800'
SET @Commission= '270'
SET @CommissionRate = (SELECT CAST(@Commission/@Sales AS VARCHAR(200)) + '%')
SELECT @CommissionRate AS [Commission Rate]
I get this:
Commission Rate
0.15000000000000000000%
But I'm looking for
.15%
Any help would be greatly appreciated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SQLSACT SSC-Insane Points: 21838 More actions · Answer 1

Try this

DECLARE @Sales DECIMAL (18,2)

DECLARE @Commission DECIMAL (18,2)

DECLARE @CommissionRate VARCHAR(200)

SET @Sales = '1800'

SET @Commission = '270'

SET @CommissionRate = (SELECT CAST(@Commission/@Sales AS VARCHAR(200)) + '%')

SELECT substring (@CommissionRate,2,3)+'%' AS [Commission Rate]

drew.allen SSC Guru Points: 76989 More actions · Answer 2

The problem occurs because of how the precision and scale of the result are determined from the precision and scale of the operand expressions. Check Precision, Scale, and Length for more information. When you divide one decimal(18,2) by another, the resulting value is decimal(38,20). The simplest way to get the result you want is to simply cast the decimal(38,20) to decimal(18,2) before casting to varchar.

NOTE: This will produce a leading zero, because there has to be at least one digit before the decimal point.

QUESTION: Don't you want to multiply by 100 before converting to varchar?

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

Drew,

I was multiplying by 100 in my original code.

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

DECLARE @Sales DECIMAL (18,2)

DECLARE @Commission DECIMAL (18,2)

DECLARE @CommissionRate VARCHAR(200)

SET @Sales = '1800'

SET @Commission = '270'

SET @CommissionRate = (SELECT CAST(CAST(@Commission/@Sales* 100 AS DECIMAL (18,2)) AS VARCHAR(200)) + '%')

SELECT @CommissionRate AS CommissionRate

15.00%

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

drew.allen SSC Guru Points: 76989 More actions · Answer 5

You realize that this is a presentation issue and is best handled in the presentation layer.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

drew.allen (12/29/2011)
You realize that this is a presentation issue and is best handled in the presentation layer.
Drew

I guess that it depends because you may not always have a presentation layer to easily address certain formatting issues.

What if you are using SSIS and Excel is the Destination?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

drew.allen SSC Guru Points: 76989 More actions · Answer 7

Welsh Corgi (12/29/2011)
drew.allen (12/29/2011)
You realize that this is a presentation issue and is best handled in the presentation layer.
Drew
I guess that it depends because you may not always have a presentation layer to easily address certain formatting issues.
What if you are using SSIS and Excel is the Destination?

This is not a hard and fast rule. It depends a lot on the capabilities of the various tools in the process. I hate SSIS's limited date formatting options, so I will often use T-SQL to format my dates.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA