Solved

Error converting data type varchar to numeric

Posted on 2011-02-26
2
951 Views
Last Modified: 2012-05-11
Hello,

I have a select statement that I keep getting an "Error converting data type varchar to numeric" and I cannot figure out why I am getting this statement or to even have it run complete without returning the error.  I have am using the same cast statement in an insert procedure and I don't get the error.  The data type of t.fullrecord is char(125).  I am using a substring to select the 20 digit UPC code from the t.fullrecord data field.  I acutally get this error on both Cast statements even when I seperate them out.  If I drop the second Cast statement that is in the Where clause and run the procedure it will start diplaying data and then blank i out and give me the error message.

Any ideas how I can figure out which records are giving me the error?  And/or how I can have the script run without rutruning the error message and just give me the valid data reocrds?  I would really like to be able to figure out which records are causing the error.

-- begin script code

SELECT t.StoreId, t.TransDate
    ,CAST(SUBSTRING(t.FullRecord,22,20) AS DECIMAL(20,0))   -- UPC
    ,t.FullRecord
    ,SUM(CAST(SUBSTRING(t.FullRecord,53,2) AS SmallInt))      -- Qnty
FROM dbo.TLogAscii t
WHERE t.RecordCode IN (SELECT Code FROM TransCode WHERE TransCodeId     BETWEEN 37 AND 52)
      and t.TransDate BETWEEN '1/2/11' AND '1/15/11'      
      AND CAST(SUBSTRING(t.FullRecord,22,20) AS DECIMAL(20,0)) IN
                        (7230200051, 7230200058, 4679826161, 6638018025, 15033
                        ,3810001617, 3810011144, 7910092561, 7419860964
                        ,64046101044, 3810013059, 72363388628, 74217400800, 9498379156)

---- end script code

Thanks in advance for the help.
0
Comment
Question by:JohnJMA
[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
2 Comments
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 500 total points
ID: 34989164


try running this query

select FullRecord
from TLogAscii
where ISNUMERIC(SUBSTRING(FullRecord,22,20)) <> 1

Should give you the offending records
0
 

Author Closing Comment

by:JohnJMA
ID: 34989435
Ewangoya, thanks, I kept using the CAST with the ISNUMERIC.. ISNUMERIC(CAST(...)) and that was causing my problem  user error.  Your solution was right on and I can get the script running by using the ISNUMERIC(..) = 1 (leaving out the CAST) returns only the valid records.  So simple and I was drawing a blank.  Thanks again.

John
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.

730 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