0

While getting some data input, for below query, encountered Convertion failed error:-

SELECT a.ProcessInstanceAppianID,
       a.ProcessInstanceDescription,
       CONVERT (INT, RIGHT(a.ProcessInstanceDescription, 6))AS KPI,
       i.KeyPersonKPILead,
       p.BestPersonName                                     AS 'Lead Person'
FROM   InternalUseOnly..processinstance a
       LEFT JOIN InternalUseOnly..ProjectInput i
              ON i.KeyProjectInput = CONVERT (INT, RIGHT(a.ProcessInstanceDescription, 6))
       LEFT JOIN snledit..Person p
              ON p.keyperson = i.KeyPersonKPILead
                 AND p.UpdOperation < 2
WHERE  a.KeyProcessStream = 1827
       AND a.ProcessInstanceCompleted IS NULL
       AND a.UpdOperation < 2

It would be very aiding & adding, if anyone can guide me here.

Thanks in anticipation!

  • You'll probably need to include table definitions for anybody to help you debug this - there are several places where you could be getting a type conversion error with this query. – Avarkx Oct 2 '14 at 13:31
1

The first thing I would do is identify rows where those 6 characters can't possibly be an integer.

SELECT ProcessInstanceAppianID, ProcessInstanceDescription
  FROM InternalUseOnly.dbo.processinstance
  WHERE RIGHT(ProcessInstanceDescription, 6) 
    NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]';

Then fix that data or somehow exclude it from the results. However this gets tricky because you have no control over whether SQL Server will try the conversions before or after the filter (even if the filter is in a CTE or subquery). So you could dump the filtered results to a #temp table, for example, and then run the conversions on the output.

In SQL Server 2012+ you can use TRY_CONVERT() instead.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.