How to correct this T-SQL syntax to get an output with no decimal places?

Question

I am using SQL Server 2016 and I have the following T-SQL code in my query:

CAST(ROUND([Count of Bookings] * 100.0 / SUM([Count of Bookings]) OVER (PARTITION BY [Market Final], [PropertyCode]), 0) AS NVARCHAR(15)) + '%'

An example of the current output of this code is: 40.000000000000%

I was expecting the output to be: 40%

As a note (I don't know if this is relevant): if I change the number in the nvarchar(x) to lower than 15, I get the following error:

Arithmetic overflow error converting expression to data type nvarchar.

Either increase the size of your varchar or first convert it to an int to drop the trailing 0's. — Sean Lange, Oct 11 '16 at 13:41

score 2 · Accepted Answer · answered Oct 11 '16 at 13:43

Use str() instead of cast():

str(round([Count of Bookings] * 100.0 /
          sum([Count of Bookings]) over(PARTITION BY [Market Final], [PropertyCode]
         ) , 0), 3, 0)  + '%'

Actually, I think str() rounds by default (could the documentation be any less clear on this subject?):

str([Count of Bookings] * 100.0 /
    sum([Count of Bookings]) over (PARTITION BY [Market Final], [PropertyCode]
                                  ), 3, 0)  + '%'

score 0 · Answer 2 · answered Oct 11 '16 at 13:42

The return type of any input of the ROUND() function is depending on the input data type as you can see on MSDN.

This causes your ROUND() to return a data type with a decimal point (in this calculation a float) that you will have to truncate after the conversion to nvarchar (or cast it to an int before).

How to correct this T-SQL syntax to get an output with no decimal places?

2 Answers2