?
Solved

Error converting data type varchar to float

Posted on 2014-02-18
3
Medium Priority
?
2,011 Views
Last Modified: 2014-02-19
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.
0
Comment
Question by:ochness
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 total points
ID: 39868794
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39869190
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
 
LVL 1

Author Closing Comment

by:ochness
ID: 39870366
Perfect...thank you. It was the xdmp.[fms #] field that was the float. A quick CAST to a VARCHAR and everything ran great.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question