Convert data from float to string error

[Han]

Hi all,

I want to convert float to string in one of my table and keep getting error
"Arithmetic overflow error for type varchar, value = 1077721.000000". What
did I do wrong?

This is my query:
select conver(varchar(10), VendorNum as VendorNum
from Vendors

Thanks alot in advance.

Han.

[Roy Harvey (SQL Server MVP)]

I ran a test to see what the float value posted would convert to.

declare @f float
set @f = 1077721.000000

SELECT convert(char(20),@f)

--------------------
1.07772e+006

That string is 12 characters long, but in the code sample posted the
convert tries to fit it into CHAR(10), and it will not fit.

Roy Harvey
Beacon Falls, CT

On Mon, 17 Nov 2008 08:38:14 -0800, Han

Post by Han
Hi all,
I want to convert float to string in one of my table and keep getting error
"Arithmetic overflow error for type varchar, value = 1077721.000000". What
did I do wrong?
select conver(varchar(10), VendorNum as VendorNum
from Vendors
Thanks alot in advance.
Han.

[Han]

Thanks.

Post by Roy Harvey (SQL Server MVP)
I ran a test to see what the float value posted would convert to.
--------------------
1.07772e+006
That string is 12 characters long, but in the code sample posted the
convert tries to fit it into CHAR(10), and it will not fit.
Roy Harvey
Beacon Falls, CT
On Mon, 17 Nov 2008 08:38:14 -0800, Han

Post by Han
Hi all,
I want to convert float to string in one of my table and keep getting error
"Arithmetic overflow error for type varchar, value = 1077721.000000". What
did I do wrong?
select conver(varchar(10), VendorNum as VendorNum
from Vendors
Thanks alot in advance.
Han.

[Russell Fields]

Han,

The string is too short. For example, the following does not fail.

select conver(varchar(20), VendorNum) as VendorNum
from Vendors

But I am astonished that VendorNum is implemented as float instead of
integer. Surely you don't want scientific notation for the VendorNum. (If
you could, changing the datamodel to INT would probably make life simpler in
the long run.)

If you are stuck with FLOAT basically being a container for INT, then
perhaps:

select conver(varchar(10), convert(int,VendorNum)) as VendorNum
from Vendors

FWIW,
RLF

Post by Han
Hi all,
I want to convert float to string in one of my table and keep getting error
"Arithmetic overflow error for type varchar, value = 1077721.000000".
What
did I do wrong?
select conver(varchar(10), VendorNum as VendorNum
from Vendors
Thanks alot in advance.
Han.