venki (2/20/2009)
Hi,I have a negative numbers in one of the column. Actually that is temperature min_temp.
Min_Temp floatIn another table, the datatype is varchar for this data. I want to transfer that varchar data to Float data. But in source, the negative numbers are there like -20, -30.8 ........
The transfer is failing and showing the error
Msg 8114, Level 16, State 5, Line 1Error converting data type varchar to float.
I used the CAST function to convert but not working.
Is there any way to fix this? Please tell me it is very urgent?
Before you cast/import, try this. I had an issue where there was data being imported from an external source (generated by SAP/Oracle) that had some hidden control characters in it and spaces as well. You could try something like:
select * from Source_Table where VarCharColumn like '%[^0-9.-]%' -- make sure '.' comes before '-'
The ^ means show me anything not a number, decimal point, or negative sign. I've noticed that whenever there is a char/varchar column that holds numeric values, some non numeric values inevitably get inside (such as spaces and end of line characters). Once the select statement returns the affected rows, hopefully not too many, you can update them manually which is what I do.
Here's a setup you can do to test this:
create table Source_Table
(
val varchar(10)
)
insert into Source_Table values('100.00')
insert into Source_Table values('101.01' + char(4)) -- char(4) picked arbitrarily, you can use anything under 32
insert into Source_Table values(char(4) + '102.02')
insert into Source_Table values('-103.03')
insert into Source_Table values('104.04 ') -- space is here
select * from Source_Table
where val like '%[^0-9.-]%' -- make sure '.' comes before '-'
-- drop table Source_Table
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein