Why do I have to cast a float to a decimal and then a decimal into a varchar?

Question

Please see the DDL below:

create table #TestFloat (id int, floattest float)
insert into #TestFloat values (1,65478)
insert into #TestFloat values (1,65478888)
insert into #TestFloat values (1,42)

How would I cast the float values to a varchar? I have tried this:

select top 1 cast(floattest as varchar(15)) from #TestFloat

which produces this:

6.54789e+007

and this:

select top 1 id,Str(floattest, 15, 0) from #TestFloat

'          65478'

I have put quotes in the above to demonstrate that it is casted into a char.

This works (taken from this question: How to convert Float to Varchar in SQL):

select CAST(CAST(floattest AS DECIMAL(20)) AS VARCHAR(20)) from #TestFloat

Why do I have to cast into a decimal first?

Answer 1

How about

SELECT FORMAT(floattest,'R') FROM #TestFloat

(Or 'G' or 'N' depending on your use) https://msdn.microsoft.com/library/dwhawy9k.aspx

or

SELECT LTRIM(STR(floattest,8,0)) FROM #TestFloat

(Pick your precision and scale to suit but watch out for rounding) https://msdn.microsoft.com/en-AU/library/ms189527.aspx

You also ask "why"? You should probably have a read of the MSDN pages on SQL Server types starting here: https://msdn.microsoft.com/en-AU/library/ms187752.aspx and this answer Difference between numeric, float and decimal in SQL Server

Answer 2

You can use convert function in sql server

select top 1 CONVERT (NVARCHAR(15),floattest) from #TestFloat

Answer 3

First cast to decimal and then to varchar.

Query

SELECT CAST
       (
           CAST(floattest AS DECIMAL(25,0)) 
           AS VARCHAR(MAX)
       ) 
FROM #TestFloat;

SQL Fiddle demo