How to convert float to char

I imported an Excel table with only one column which is with Part_No of 8 digit numeric, such as 10101241, etc. After imported, the data type in SQL becomes float and I have a trouble to convert the data type from float to char. Here are the results I got:

SELECT part_no FROM tab
shows the exactly 8-digit part numbers listed but they are in float data type and I cannot do any joint using it even they look fine.

SELECT CONVERT(CHAR(20), part_no) results in 1.01002e+007 for the first record 10101241.

SELECT CONVERT(CHAR(8), part_no) results in the error:
"Arithmetic overflow error for type varchar, value=10100241.000000."

If I got into the table and change the column from float to CHAR(20), all part numbers become 1.01002e+007, etc.

What should I do? Please help.
CastlewoodAsked:
Who is Participating?
 
AshokConnect With a Mentor Commented:
SELECT convert(char(20),convert(numeric(12),part_no)) FROM TABLE1

or

SELECT convert(char(20),convert(numeric(12,0),part_no)) FROM TABLE1
0
 
dannygonzalez09Connect With a Mentor Commented:
try doing this

SELECT CONVERT(CHAR(20), CONVERT(INT,part_no))
0
 
Lee SavidgeCommented:
I'd reimport but during the import modify the data mapping to change the default data type to an int if you want an it, or string if you need a string.
0
 
CastlewoodAuthor Commented:
So I need to convert the float to int before further converting to char.
Also I found there is no why to change the data type in the import wizard.
0
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.

All Courses

From novice to tech pro — start learning today.