Solved

Error converting data type varchar to numeric

Posted on 2011-02-26
2
952 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

Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

717 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