TSQL Convert FLOAT to STRING Truncation/Rounding Issue
1
vote
1
answer
17267
views
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
Asked by Mazhar
(823 rep)
Apr 27, 2017, 08:23 AM
Last activity: Apr 21, 2020, 11:26 AM
Last activity: Apr 21, 2020, 11:26 AM