SQLTeam.com | Weblogs | Forums

Type conversion issue


#1

i have two tables TABLEA and TABLEB

in TABLEA , i have some column where float values are stored but column type is varchar , but when i tried to insert
values from tablea to table by using following statement it is showing conversion error.
Tableb have float type of column where i want to store those columns data from tablea.

insert into tableb(varcharcol, varcharcol, floatcol , )
select varchardata, varchardata , floatdatastoredinvarcharcol, floatdatastoredinvarcharcol
from tablea

please help.


#2

corrected statement

insert into tableb(varcharcol, varcharcol, floatcol ,floatcol )
select varchardata, varchardata , floatdatastoredinvarcharcol, floatdatastoredinvarcharcol
from tablea


#3

please post the DDL for the tables involved


#4

Since the column is varchar, and not actually float, it's very likely bad values have gotten into the table that can't be converted to float. You'll have to test the data in that column to find the bad data before you load it to the float column.


#5

Find the moron that did this to you, get him fired, run over him in the parking lots. We cannot hurt anyone else and rewrite all of his code

Floating-point numbers have rounding errors inherent in their structure. Casting and converting strings gets the same sort of errors as floating-point math. But it is even worse in SQL!

We never required that SQL properly handle floating-point numbers, like the Fortran standards did. I do not know of a single implementation that does, in fact. All that ANSI X3H2 wanted to do was store them. We made no provisions for infinities, "not a number", normalizations and all that other good stuff.

Can you switch to decimal (S, P) instead?