Conversion Failed When Converting - Understand But Stuck!

1

I have a simple Sql Query as follows:
SELECT product_id, product_desc, product_long_desc
FROM cm_products
WHERE product_id in (180515,180940,180945,180950,180955,180960,182105,182106.................................)

Running the query produces the following error: Conversion failed when converting the varchar value 180110"" ' to data type int.

Having spent a couple of hours on this I understand that there is some sort of data type mismatch. product_id is char(30), product_desc(nvarchar(100) and product_long_desc is nvarchar(2000). Think I need to add CAST or Convert into query but cannot work out how to do it. Also I dont understand why the query stalls on 180110 as it is not in my list?

Grateful if someone could show me how to solve this problem.

2

can you try this
where cast(productid as int) in()

1 Like
3

Thank you for the help. Where do I put that expression?

SELECT product_id, product_desc, product_long_desc
FROM cm_products
WHERE cast(productid as int) in() in (180515,180940,180945,180950,180955,180960,182105,182106.................................)?

4

yes,your query should work now

1 Like
5

SELECT product_id, product_desc, product_long_desc
FROM cm_products
WHERE cast(product_id as int)in(180515,180940,180945,180950,180955,180960,245151.......)

Still gves arror: Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '180110"" ' to data type int.

6

Do not convert the data column, convert the IN list:

WHERE product_id IN ('180515','180940','180945','180950','180955','180960','182105','182106',...)

1 Like
7

Brilliant - thanks ScottPletcher - it worked.