Why is casting from float to varchar being rounded in SQL Server?

Question

The 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 'float' and 'real' (Transact-SQL).

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

Solution 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.

Solution 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

Solution 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

Reference: CAST and CONVERT (Transact-SQL)

Or with STR():

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

It 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.

Comments

[N30]

I know that float is approx. type but what exactly precision of 15 digits mean? It is easy to advise that don't use float but there are scenarios where we can not exclude float usage. e.g. legacy app

[JNK]

@N30 - if you have anything longer than 15 digits, it won't be stored. It may try to recalculate it but it won't be an accurate figure.

[N30]

@JNK that is exactly what I am asking if value is shorter than 15 digits e.g. 1.353954 than converting it to varchar(40) should be like 1.353954 then 0s upto 15th digit and then approx sequence but it is rounded after 6 characters. I will try with STR() and let you know.

[N30]

@JNK i guess after reading your comment second time, i got it.. it is being stored upto 15 digits but still stored as approximation.

[blindguy]

str() just has a different default setting that convert. Both can be modified to show more or less precision. STR (float_expression [ , length [ ,decimal ] ] ) msdn.microsoft.com/en-us/library/ms189527.aspx

[SQLServerSteve]

The Convert method is fantastic, great answer. Most other answers to this question on StackExchange and many other sites revolve around casting to decimals, which isn't an option if you don't know the precision and scale in advance.