T-SQL Rounding
Filed under ColdFusion , SQL , SQL Server
I was trying to format percentages and could not figure out why the number was rounding up to the nearest integer. I did a <cfquery> and did my percentage calculations within a SELECT statement. I then tried <cfoutput> and <cfdump> still showing only integers. I proceeded to try LSNumberFormat and made sure my variables in T-SQL were set to allow decimals. I still had no luck of removing the rounding problem.
I found that the problem has nothing to do with ColdFusion but rather it is an T-SQL thing. When T-SQL does a calculation of only integers it rounds to the nearest integer. To get around this problem all you need to do is multiply (*) one of your variables by 1.00 and the T-SQL will then output decimals. Here is a quick sample:
<cfquery name=”test” datasource=”sql_test”>
SELECT ((((SUM(var2)*1.00)-SUM(var1)) /SUM(var2))*100) as Percentage
FROM tblAudit A
</cfquery>
As you can see I multiplied the whole thing by 100 to display in percentage format. You can then use ColdFusions formatting to display it however you or the managers want it ;) I’m sure this is not the only way and I would love to know of another. This little problem made me feel pretty stupid!
Aug22
