CODEWITHSUNDEEP
sqlsql-servert-sqlvarcharmax
Bellash
Bellash

Reputation: 8204

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

Upvotes: 0

Views: 3582

Answers (2)

LuisR9
LuisR9

Reputation: 116

try this:

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

Upvotes: 0

Vladimir Baranov
Vladimir Baranov

Reputation: 32703

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.

Upvotes: 0

Related Questions