SQL Server – Convert FLOAT to STRING Truncation/Rounding Issue

floating pointsql serversql-server-2008-r2t-sqltype conversion

SQL Server 2008 R2

Why when converting a FLOAT to a string (Varchar / Nvarchar) using Cast or Convert, does a rounding or truncation occur?

Using CAST

DECLARE @floatVal FLOAT = 4.76758527755737

SELECT
    CAST(@floatVal AS VARCHAR(20)) 'VARCHAR',
    CAST(@floatVal AS NVARCHAR(20)) 'NVARCHAR'

Output

VARCHAR NVARCHAR
4.76759 4.76759

Using CONVERT:

DECLARE @floatVal FLOAT = 4.76758527755737

SELECT
    CONVERT(VARCHAR(20), @floatVal) 'VARCHAR',
    CONVERT(NVARCHAR(20), @floatVal) 'NVARCHAR'

Output

VARCHAR NVARCHAR
4.76759 4.76759

With STR however, there's no truncation/rounding

DECLARE @floatVal FLOAT = 4.76758527755737

SELECT
    LTRIM(STR(@floatVal, 17, 14)) 'STR'

Output

STR
4.76758527755737

Thanks in advance

Best Answer

From the documentation about CAST and CONVERT:

enter image description here

CAST is 'apparently' using the default style of 0 since you cannot specify a style when using CAST.

CONVERT defaulted to style 0 since you did not specify a style.

You can achieve more precision using CONVERT and style (and your have to increase the length of the target datatype), but the results appear to be in scientific notation (so, not exactly like STR). 

DECLARE @floatVal   FLOAT = 4.76758527755737

SELECT
    CONVERT(VARCHAR(30), @floatVal,1)     'VARCHAR'
    ,CONVERT(NVARCHAR(30), @floatVal,1)   'NVARCHAR'
GO

enter image description here

Related Question