asked on

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

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?

Victor Spiridonov

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

Victor Spiridonov

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

tomnich

ASKER

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.

Victor Spiridonov

You can't have both when converting to varchar.

ASKER CERTIFIED SOLUTION

gmoriak

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tomnich

ASKER

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

Victor Spiridonov

As I said you can't have both on 6.5

gmoriak

I have SQL 6.5 not 7.0 and it worked fine.

gmoriak

What didn't work for you?

tomnich

ASKER

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

gmoriak

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))

tomnich

ASKER

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