Error converting data type varchar to float

I am getting the following error when trying to run an update query: Error Converting Data Type Varchar to Float. Here is the code I'm using...

begin tran

update TB_PARTS
set COST = xdmp.new_cost
--set COST = case isnumeric(xdmp.new_cost) when 1 then cast(xdmp.new_cost as FLOAT) else null end
from TB_STYLES
join TB_PARTS on TB_STYLES.STYLE_ID = TB_PARTS.STYLE_ID
join xdmp on TB_STYLES.STYLE = xdmp.[fms #]
where
      TB_PARTS.CONTACT_ID = 23
      
rollback
commit

The data in the xDMP table was imported from an Excel spreadsheet using DTS. The data types for the TB_PARTS.COST field and the xDMP.New_Cost are both showing FLOAT.

Not sure what I'm doing wrong. any suggestions to help fix the issue would be great.

Thanks.
LVL 1
ochnessAsked:
Who is Participating?
 
Surendra NathTechnology LeadCommented:
check the data types for

TB_STYLES.STYLE_ID & TB_PARTS.STYLE_ID

and also

TB_STYLES.STYLE & xdmp.[fms #]

one of them may be a float and the other is not....
0
 
Anthony PerkinsCommented:
It has to do with formatting.  For example, Excel allows negative numbers to be represented as (123456) and thousands as 123,456.  SQL Server is not so forgiving.

You are going to have to roll up your sleeves and identify the culprit and fix it.
0
 
ochnessAuthor Commented:
Perfect...thank you. It was the xdmp.[fms #] field that was the float. A quick CAST to a VARCHAR and everything ran great.
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.