Solved

Error converting data type varchar to numeric

Posted on 2011-02-26
2
948 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
2 Comments
 
LVL 32

Accepted Solution

by:
ewangoya 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard 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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

785 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