I understand that float numbers are inaccurate. Why are floating point numbers inaccurate?.

My question is why a simple cast to a varchar(max) will sometimes lose precision, and why this seems to happen only when there are five digits before the point.

For instance when the float has more than four digits before the point. The second digit after the decimal is sometimes lost. For instance.

declare @p FLOAT(53)
set @p=10080.12
select @p,cast(@p as VARCHAR(max))

10080.12 10080.1

Here the float has four digits before the point and it works.

declare @q float(53)
set @q=9980.12
select @q,cast(@q as VARCHAR(max))

9980.12 9980.12

I note that Microsoft recommends using STR and not CAST, but I would still like to understand why this is happening. And anyway Microsoft's recommendation does not say that precision will be lost.

When you want to convert from float or real to character data, using the STR string function is usually more useful than CAST( ). This is because STR enables more control over formatting. For more information, see STR (Transact-SQL) and Functions (Transact-SQL).

CodePudding user response：

From Microsoft doc for CAST and CONVERT :

Syntax

CAST ( expression AS data_type [ ( length ) ] )

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

...

float and real styles

For a float or real expression, style can have one of the values shown in the following table. Other values are processed as 0.

Value	Output
0 (default)	A maximum of 6 digits. Use in scientific notation, when appropriate.
1	Always 8 digits. Always use in scientific notation.
2	Always 16 digits. Always use in scientific notation.

So default 0 style is used, which is 6 digits max. Which seems unavoidable with CAST.

To avoid rounding for more digits one may use CONVERT instead, and pass a non-zero style, like:

CONVERT(VARCHAR(max), @q, 1)