Error converting data type varchar to numeric

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.
JohnJMAAsked:
Who is Participating?
 
Ephraim WangoyaCommented:


try running this query

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

Should give you the offending records
0
 
JohnJMAAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.