1

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

4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.