SQL Conversion failed when converting the varchar value to data type int

Question

I have this query:

SELECT C.CustomerID, 
       Q.Question_ID, 
       Q.Department,  
       C.DueDate  
FROM homefront.dbo.TPM_Questions_Default AS Q  
LEFT OUTER JOIN homefront.dbo.CustomerQuestions AS C 
    ON Q.Question_ID = C.QuestionID 
INNER JOIN tblCustomers T 
    ON CONVERT(INT, CONVERT(VARCHAR(MAX), T.Customer_No )) = C.CustomerID 
WHERE C.DueDate <= GETDATE() AND C.DateCompleted IS NOT NULL

I just added in the INNER JOIN tblCustomers T ON CONVERT(INT, CONVERT(VARCHAR(MAX), T.Customer_No )) = C.CustomerID

Now I get this error:

Conversion failed when converting the varchar value 'C000432' to data type int.

What did you expect? And what do you want? (When you design a database, use the same datatype for columns supposed to be used when joining tables...) — jarlh, Jan 28 '16 at 14:51
I am expecting the get the INNER JOIN to work with no errors. — user979331, Jan 28 '16 at 14:58
You can't cast non-numeric values to integer. Perhaps you can do the opposite, i.e cast the other column to char(11) instead. — jarlh, Jan 28 '16 at 14:59
You have a non integer you are trying to cast to an integer, [this](http://stackoverflow.com/questions/106206/fastest-way-to-remove-non-numeric-characters-from-a-varchar-in-sql-server) might help — Gilgamesh, Jan 28 '16 at 15:00
Your Customer_No contains a record value of C000432 and it causes error when you do CONVERT(INT, CONVERT(VARCHAR(MAX), T.Customer_No )), probably you want to remove the character C inside the C000432? — Budianto IP, Jan 28 '16 at 15:03

xan · Accepted Answer · 2016-05-09T08:56:18.207

You are converting (casting) a column containing what appears to be a character values to int values

CONVERT(INT, CONVERT(VARCHAR(MAX), T.Customer_No ))

Since the value C000432 is not a valid INT, you are getting an error. There's several things to consider:

Is customer number supposed to be a char / varchar data type and if so, are non integer characters expected / allowed in the data?
If not:
1. has some incorrect data snuck in (the C character) - test data?
2. Why isn't it an int column to begin with (to make the DB enforce this)
If so, why are you casting it to an int?
- Is the table you are joining to using a integer identifier?
- Do you need to strip off the leading 'C' Character before casting to an int?

SQL Conversion failed when converting the varchar value to data type int

1 Answers1