Search code examples
sqlsql-servert-sqlvarcharmax

Smart CONVERT float to VARCHAR in T-SQL

I have the following code

DECLARE @m FLOAT=213456789.55
DECLARE @sql VARCHAR(MAX)='INSERT INTO Test VALUES('+CONVERT(VARCHAR,@m,1)+')'
EXEC(@sql)

but the result is 213456790 instead of 213456789.55

When I try to write CONVERT(VARCHAR,213456789.55,1) it then returns 213456789.55 not 213456790

How do I solve this?

EDITS

Declaring @m as Decimal like following DECLARE @m DECIMAL(18,2)=213456789.55 solved the issue but I want to know if there is another solution for using float. Thanks

Solution

  • You can use STR instead of CONVERT. STR allows to specify number of decimal places to the right of the decimal point.

    DECLARE @m FLOAT=213456789.55;
SELECT 
@m AS [Float]
,CONVERT(VARCHAR(100),@m,0) AS Convert0
,CONVERT(VARCHAR(100),@m,1) AS Convert1
,CONVERT(VARCHAR(100),@m,2) AS Convert2
,LTRIM(STR(@m, 20, 2)) AS [Str]

    Result (SQL Server 2008)

    +--------------+--------------+----------------+------------------------+--------------+
|    Float     |   Convert0   |    Convert1    |        Convert2        |     Str      |
+--------------+--------------+----------------+------------------------+--------------+
| 213456789.55 | 2.13457e+008 | 2.1345679e+008 | 2.134567895500000e+008 | 213456789.55 |
+--------------+--------------+----------------+------------------------+--------------+

    CONVERT always uses scientific notation for float types.