How to convert float to char
Posted on 2014-01-09
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.