asked on
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.
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.
Microsoft SQL Server 2008Microsoft SQL Server 2005Microsoft SQL Server
Last Comment
Castlewood
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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.
Also I found there is no why to change the data type in the import wizard.