Why casting from float to varchar is being rounded

问题:

Following sql

declare @a as float, @b as float

select @a=1.353954 , @b=1.353956
select 
CAST(@a as VARCHAR(40)) AS a_float_to_varchar ,
CAST(@b as VARCHAR(40)) AS b_float_to_varchar

results in

a_float_to_varchar                       b_float_to_varchar
---------------------------------------- ----------------------------------------
1.35395                                  1.35396

based on

http://msdn.microsoft.com/en-us/library/ms173773.aspx

float has precision of 15 digits so I am not sure why the number is being rounded when converted to varchar

回答1:

Also from your link (it's actually the first line):

Approximate-number data types...

If you want exact precision, don't use float.

That being said, there is a function STR() specifically for converting float to a character data type.

回答2:

cast to decimal before casting to varchar:

declare @a as float, @b as float

select @a=1.353954 , @b=1.353956
select 
CAST(CAST(@a AS DECIMAL(38,18)) as VARCHAR(40)) AS a_float_to_varchar ,
CAST(CAST(@b AS DECIMAL(38,18)) as VARCHAR(40)) AS b_float_to_varchar

回答3:

You can specify style to include more digits.

declare @gg float
set @gg = 124.323125453
SELECT @gg,Convert(varchar, @gg,128)

for newer versions of sql server use SELECT @gg,Convert(varchar, @gg,3)

returns

124.323125453   124.323125453

https://msdn.microsoft.com/en-us/library/ms187928.aspx

or with STR()

declare @gg float
set @gg = 124.323124354234524
SELECT @gg,str(@gg,16,15)

should give you all the possible digits. 16 is the total possible length (includes period) while 15 places after the decimal is possible (actually 0.2323... the 0 count toward length, so the length needs to be 17 if all numbers are less that 1) STR() however pads the results with leading spaces and trailing 0.