user979331 user979331 - 7 months ago 53
SQL Question

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

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.

xan xan
Answer

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?