Solved

Error converting data type varchar to float

Posted on 2014-02-18
3
1,928 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
3 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 125 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now