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?

Accepted Answer

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
```

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

Solution

Problem