SQL Server Conversion Error – Fixing Varchar to Int Conversion Failure

sql server

I inner join 2 table (Termination Reasons with Users) were the two talk to the each other and Termination Reasons table contains 2 column (Id (int) , Name(varchar)) and the other one , Users Table contains 1 column (EmploymentStatusId (int)) .

I can use EmploymentStatusId(int) from tbl Users with Id (int) from another table wich is Termination Reasons beacuse , both of them have same data type (int):

SELECT 
u AS Status

FROM 
d AS p INNER JOIN d AS u ON u=p
INNER JOIN d AS t ON t = u
WHERE u=214 order by u;

But when i try using EmploymentStatusId (int) from tbl Users with Name (varchar) from tbl Termination Reasons , i got problem:

SELECT
t AS Status

FROM 
d AS p INNER JOIN db AS u ON u.I=p
INNER JOIN b AS t ON t= u
WHERE u=214 order by u;

And then Error :
Conversion failed when converting the varchar value 'TerminatedByCompany' to data type int.

i know why ,because they have different data type , therefore they can not talk to the each other.
but i tried also to solve this with Convert but idont know ,why or how is not working.Can someone please help or point me in the right direction. Thanks.

The Query ,where i tried with CONVERT ,its look like this:

SELECT
CONVERT(INT, CONVERT(VARCHAR(12), t)) as Status

FROM 
db AS p INNER JOIN d AS u ON u =p 
INNER JOIN db AS t ON t.Name = u
WHERE u=214 order by u.FirstName;

Best Answer

I am not entirely sure what you are trying to achieve by joining on t.Name and u.EmploymentStatusId as they are different data types, varchar and int. When this join is being completed it is trying to implicitly convert t.Name into an int and it contains data that cannot be converted as the error message shows.

If you are trying to get the employment status of the user with CustomerId of 214 I would do something similar to:

 SELECT
 t.Name AS Status

 FROM 
 dbo.Paychecks AS p INNER JOIN dbo.Users AS u ON u.Id=p.UserId 
 INNER JOIN dbo.TerminationReasons AS t ON t.Id = u.EmploymentStatusId
 WHERE u.CustomerId=214 order by u.FirstName;

If I have missed the point please clarify exactly what you are trying to achieve.

Related Solutions

Sql-server – Getting “Conversion failed when converting the nvarchar value to data type int.”

Write your query this way. It includes a better test for integers and dumps ISNUMERIC that returns 1 for '-.', for example.

SELECT
    zip,
    coordinates
FROM (
    SELECT
        CASE WHEN ZIP5>'' AND NOT ZIP5 LIKE '%[^0-9]%' THEN
            CONVERT(int, [ZIP5]) END zip,
        CONVERT(varchar(max), geom) AS coordinates
    FROM 
        [SpatialData].[dbo].[zip5]
    WHERE
        ZIP5>'' AND NOT ZIP5 LIKE '%[^0-9]%'
) AS t1
WHERE
    zip >= 85000 AND
    zip < 86000

See this Connect item

SQL Server is free to evaluate the WHERE/SELECT clause in the order that it decides is optimized. A view or derived table is not materialized can easily be expanded into, from the outer query.

What SQL Server is compiling is really a query that looks like this

SELECT
    CONVERT(int, [ZIP5]) AS zip,
    CONVERT(varchar(max), geom) AS coordinates
FROM 
    [SpatialData].[dbo].[zip5]
WHERE
    ISNUMERIC([ZIP5]) = 1
    AND CONVERT(int, [ZIP5]) >= 85000
    AND CONVERT(int, [ZIP5]) < 86000

You can inspect the query plan of your original, but my guess from looking at the structure is that the WHERE clause uses the expression CONVERT(int, [ZIP5]) twice, so it makes sense to streamline the resolution (calculation to a result) of the expression in the process of retrieving data from the table. This puts the processing of the SELECT clause before the WHERE, so your ISNUMERIC() = 1 never got a chance to filter the bad eggs.

It is by-design.

Sql-server – Conversion failed when converting the varchar value to data type int in SQL Server

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.

Best Answer

Related Solutions

Sql-server – Getting “Conversion failed when converting the nvarchar value to data type int.”

Sql-server – Conversion failed when converting the varchar value to data type int in SQL Server

Related Question