SQL Server 2005 : str(4.65,5,1) = 4.7, str(3.65,5,1) = 3.6?

Question

In SQL Server 2005 str() behaves strange on some float values while rounding. While searching in net, I found the below code and explanations there.

select STR(4.65,5,1) -- it will give 4.7
select STR(3.65,5,1) -- it will give 3.6

I got some explanations here and here, but didn't get anything from there (that above T-SQL taken from one of the explanations link)

Could anyone please explain why it behaves like this?

Answer 1

Syntax of STR(); STR ( float_expression [ , length [ , decimal ] ] ) clearly says that the number is a float_expression. Therefore whatever the number you give it will be first converted to a FLOAT(n) where default value of n = 53.

So

SELECT STR(4.65,5,1), SELECT STR(3.65,5,1)  

Equal to:

SELECT STR(CAST(4.65 AS FLOAT(53)),5,1) , STR(CAST(3.65 AS FLOAT(53)),5,1)

If you specify n, say n = 4 it will give the answer you are expecting (ie; 4.7 and 3.7)

SELECT STR(CAST(4.65 AS FLOAT(4)),5,1) , STR(CAST(3.65 AS FLOAT(4)),5,1)
              --4.7,                     3.7