将varchar值转换为数据类型int时，SQL转换失败

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

我刚刚添加了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.

1 个解决方案

#1

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

您正在将包含看似字符值的列转换(转换)为int值

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:

由于值C000432不是有效的INT,因此您收到错误。有几件事需要考虑:

Is customer number supposed to be a char / varchar data type and if so, are non integer characters expected / allowed in the data?

客户编号应该是char / varchar数据类型,如果是,是数据中预期/允许的非整数字符吗?

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)

如果不是:有一些不正确的数据隐藏(C字符) - 测试数据?为什么不是一个int列开始(使数据库强制执行此操作)

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?

如果是这样,为什么要将它转换为int?您要加入的表是否使用整数标识符?在转换为int之前,是否需要去除前导'C'字符?

#1