Monday, December 20, 2010

Formatting float to varchar in non scientific notation

When trying to convert a float value to varchar "Convert" returns string that represents the float value in sceientific notation.

e.g.

DECLARE @Float as float =1900000.0

SELECT CONVERT (varchar(50),@Float)

Results
---------
1.9e+006

However if you would like a string "1900000.0" you can use one of the methods below

DECLARE @Float as float =1900000.0

SELECT CONVERT(varchar(100), CAST(@Float AS decimal(38,1)))
SELECT ltrim(STR(@Float, 50, 1))

Both of them return a string
--------------------------
1900000.0