wdbates
asked on
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.
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.
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for your help
SELECT *
FROM dbo.tblHeffers
WHERE ISNUMERIC(HefferCode) = 0
If you do, that is why it's failing.
Greg