Solved

Error converting data type varchar to float

Posted on 2014-02-18
3
1,991 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 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

729 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