0

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

Vladimir Baranov
  • 31,799
  • 5
  • 53
  • 90
Bellash
  • 7,560
  • 6
  • 53
  • 86

2 Answers2

0

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.

Vladimir Baranov
  • 31,799
  • 5
  • 53
  • 90
  • Thank you it works! Except when `@m =12456354213456789.55;` – Bellash Feb 19 '16 at 12:32
  • 1
    [float](https://msdn.microsoft.com/en-AU/library/ms173773.aspx) has precision of 15 digits. So, when you assign `12456354213456789.55` to `float` variable the value is rounded. If you need more than 15 digits of precision, you have to use `decimal`. – Vladimir Baranov Feb 19 '16 at 22:05
0

try this:

DECLARE @m FLOAT=213456789.55
DECLARE @sql VARCHAR(MAX)='INSERT INTO Test VALUES('+CONVERT(VARCHAR,CAST(@m AS MONEY),1)+')'
EXEC(@sql)
LuisR9
  • 116
  • 3
  • Thank you for your answer, time and effort BUT while this can work, it is not a not idea to change the `float` type into `money`. The goal was not to change the data type (other than varchar) – Bellash Feb 19 '16 at 12:30