Solved

# CONVERT(VARCHAR(25),MyFloat) truncates to 6 signifcant figures

Posted on 1999-07-05
Medium Priority
248 Views
I'm using the convert function to convert FLOATs to VARCHARs and it seems to be truncating the decimal places to about 5 or 6.  I could use the STR function except this would give loads of trailing 0s where I wouldn't want them.  Any idea why SQL Server is behaving this way, and how  do I fix it?
0
Question by:tomnich
• 4
• 4
• 4

LVL 7

Expert Comment

ID: 1096155
You need to use the following statement to get Y of digits after decimal:
CONVERT(varchar(25),CONVERT(NUMERIC(X,Y),myfloat))
0

LVL 7

Expert Comment

ID: 1096156
You need to use the following statement to get Y of digits after decimal:
CONVERT(varchar(25),CONVERT(NUMERIC(X,Y),myfloat))
0

LVL 1

Author Comment

ID: 1096157
But this is no good becuase I get loads of trailing 0s, eg

SELECT CONVERT(varchar(25),CONVERT(NUMERIC(16,12),CONVERT(FLOAT,3.444)))

gives 3.444000000000

When I want to return 3.444.  I need 12 decimal places though for some numbers.
0

LVL 7

Expert Comment

ID: 1096158
You can't have both when converting to varchar.
0

LVL 3

Accepted Solution

gmoriak earned 400 total points
ID: 1096159
You can have both if you get creative enough.

Here you go

declare @num float
select @num = 3.444444567

SELECT convert(varchar(25),
left(CONVERT(decimal(16, 12),CONVERT(FLOAT,@num)),
patindex('%000000000000%', CONVERT(varchar(25), CONVERT(decimal(16, 12),CONVERT(FLOAT,@num))) + '000000000000') - 1))

0

LVL 1

Author Comment

ID: 1096160
That's impressive, but only works on SQL-Server 7. Sorry, forgot to mention that I need it to work on 6.5.

I tried replacing left with substring, but it disallowed doing this on a decimal
0

LVL 7

Expert Comment

ID: 1096161
As I said you can't have both on 6.5
0

LVL 3

Expert Comment

ID: 1096162
I have SQL 6.5 not 7.0 and it worked fine.
0

LVL 3

Expert Comment

ID: 1096163
What didn't work for you?
0

LVL 1

Author Comment

ID: 1096164
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'left'.
0

LVL 3

Expert Comment

ID: 1096165
I'm using ISQL 6.5 and just did a cut/paste from the proposed answer and it worked fine. In any case, here's a version using substring instead of left.

declare @num float
select @num = 3.444444567

SELECT convert(varchar(25),
substring(convert(varchar(25), CONVERT(decimal(16, 12),CONVERT(FLOAT,@num))), 1,
patindex('%000000000000%', CONVERT(varchar(25), CONVERT(decimal(16, 12),CONVERT(FLOAT,@num))) + '000000000000') - 1))

0

LVL 1

Author Comment

ID: 1096166
Thanks that's excellent.  I thought about converting the decimal to a varchar but then I thought I'd let you try it instead!!

Cheers
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.