Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Error converting data type varchar to float

Posted on 2014-02-18
3
Medium Priority
?
2,038 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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.

597 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