Converting Varchar to Numberic or int

When I run this query I receive the following error:
Error converting data type varchar to numeric.

The Query:
select [HefferTypeCode], [HefferCode], CAST([HefferCode] AS NUMERIC)
FROM dbo.tblHeffers
WHERE [HefferTypeCode] IN ( 'A' , 'BC' ) AND CAST(HefferCode AS NUMERIC) < 1000

The HefferCode runs from 001 to 2019 and if I remove the test after the AND it runs.
wdbatesAsked:
Who is Participating?
 
JestersGrindConnect With a Mentor Commented:
I don't think the 0 in the HefferCode column is causing an issue.  Although, if that is incorrect, you should update it to a correct value.  The other record is probably the cause.  Even though it is not in group A or BC, SQL is still trying to evaluate it and can't.  To fix it, you need to update that record to a numeric value.

Greg

0
 
JestersGrindCommented:
Do you get any records when you run this?

SELECT *
FROM dbo.tblHeffers
WHERE ISNUMERIC(HefferCode) = 0

If you do, that is why it's failing.

Greg

0
 
wdbatesAuthor Commented:
Yes, but when I place in the test WHERE [HefferTypeCode] IN ( 'A' , 'BC' ) I return no records.  I did more checking and I do have a record with a HefferCode of 0 in the A and BC group.  I also found a record not in the A and BC group that is alpha.  So how do I fix my task?
0
 
wdbatesAuthor Commented:
Thank you for your help
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.