Adam Machanic started the idea of a blog party last month, getting people to write about date and time issues in SQL Server. This month he’s asking for a puzzling situation, and I had a little time, so I decided to oblige.

I’ve done a lot of different things in SQL Server over the years, but this particular situation is one that stumped me for awhile. And I’m not the only one as I see this question posted on a regular basis and the answer typically is similar to what I used to solve my problem.

Years ago I was working for a small company, receiving regular data feeds from vendors that required us to load and update our list of products. We sold education based products, and as the cost, name, etc. changed from Vendors, we had to update our data.

So I had a table that was something like this one. This is simplified for the sake of the example.

CREATE TABLE MyTable
( id INT,
  PONumber VARCHAR(20),
  Cost NUMERIC(10,4)
  )

One day I had to run an update statement like this one to fix a product. It was a simple update that would correct a simple product:

UPDATE dbo.MyTable
SET Cost = 56
WHERE PONumber = 24

and I got this:

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value '24 A' to data type int.

Huh? I’m updating a specific product, and while the data types are different, this ought to be easily handled by the engine. I tried this same statement a few ways, and it kept failing.

I see many people running into this, and not understanding why it fails. After a few hours of working through this I eventually realized what was happening.

There is an implicit conversion occurring here because the data type of the PO number is a varchar, meaning that the value is

’243’

‘24’

etc.

and it’s being compared, or converted, to an int to match the 24 integer in the WHERE clause. If you examine the implicit conversion table in the CAST and CONVERT BOL entry, you’ll find that this is indeed an implicit conversion.

Does order matter? No. This returns the same error.

UPDATE dbo.MyTable
SET Cost = 56
WHERE 24 = PONumber

Why isn’t 24 converted to ‘24’ and compared? I think it has to do with data type precedence, in which int has a higher precedence than char (or varchar), so the conversion takes place with the column being converted to 24, an integer.

I fixed this by changing my query.

UPDATE dbo.MyTable
SET Cost = 56
WHERE PONumber = '24'

That removed the conversion, and things worked.

Knowing your implicit conversions is important. It’s a basic thing, but you ought to have an idea of when they occur, and you ought not to depend on them. This should be something you keep in mind, and make it like the printer power cord: check it first.

Happy T-SQL Tuesday!