Error converting data type varchar to numeric
Posted on 2011-02-26
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
,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.