Jeff Moden (8/31/2013)
It produces rounding errors when working with decimal places simply because FLOAT is based on binary math rather than decimal math and it can't actually precisely contain certain values with it's rather short Mantissa even on small numbers.
That assumes decimal values. The case in point was numeric values. If you want to use str() to get right-aligned decimal values, yes, you can get errors, and if it essential that your rounding is consistent there may be a workaround:
SELECT str(1.23955, 10, 4) AS Wrong, convert(float, 1.23955) AS Why,
str(round(1.23955, 4), 10, 4) AS Workaround
Note: you need to run the query from SQLCMD to see Why.
STR() is also more than twice as slow as RIGHT() concatenation method.
Frankly, I couldn't be bothered. I also use things like:
convert(char(6), getdate(), 112) + '01'
when I want the first day of the month, even if people claim other constructs are faster. I look at them and say, eh? I can do the most common date tricks with style 112 in my sleep.
Developer productivity counts some times. Performance in T-SQL is about greater scheme of things.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]