Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Syntax Error Converting The Varchar Value To A Column Of Data Type Int

From Wiki

Jump to: navigation, search

Let's say you have the following query

  1. SELECT * FROM SomeTable WHERE SomeColumn in (12345,22222)

And you get the "Syntax Error Converting The Varchar Value To A Column Of Data Type Int" error, you might think I am not converting anything why am I getting this error? If the query was the one below then it would make sense

  1. SELECT CONVERT(INT,SomeColumn) FROM SomeTable...

Now let's see what causes this error


  1. --Create the test table and insert 3 rows of data
  2. CREATE TABLE SomeTable (id INT,SomeColumn VARCHAR(49))
  3. INSERT INTO SomeTable VALUES(1,'12345')
  4. INSERT INTO SomeTable VALUES(1,'22222')
  5. INSERT INTO SomeTable VALUES(1,'33333')

let's select from that table

  1. SELECT * FROM SomeTable WHERE SomeColumn in (12345,22222)

That works, no problem


Let's insert something with a non numeric value

  1. INSERT INTO SomeTable VALUES(1,'33333A')


Let's try again

  1. SELECT * FROM SomeTable WHERE SomeColumn in (12345,22222)

Oops error
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '33333A' to a column of data type int.


The trick is to put quotes around the values
And now it works

  1. SELECT * FROM SomeTable WHERE SomeColumn in ('12345','22222')

Clean up

  1. DROP TABLE SomeTable


While it might seem as if the implicit conversion should occur on the right side of the = sign, it actually takes place on the left. So what SQL Server is actually trying to do is convert the table value to the value you are comparing it to, not converting the comparison value to compare it to the table value. This seems counterintuitive to most people who would expect the conversion to go the other way, but it is the way SQL Server is designed.




Here is what the error message looks like in other languages as stored in sys.messages

us_english
Conversion failed when converting the %ls value '%.*ls' to data type %ls.

Deutsch
Fehler beim Konvertieren des %1!-Werts '%2!' in den %3!-Datentyp.

Français
Échec de la conversion de la valeur %1! '%2!' en type de données %3!.

日本語
%1! の値 '%2!' をデータ型 %3! に変換できませんでした。

Español
Error de conversión al convertir el valor %1! '%2!' al tipo de datos %3!.

Italiano
Conversione non riuscita durante la conversione del valore %1! '%2!' nel tipo di dati %3!.

русский
Ошибка преобразования значения %1! "%2!" в тип данных %3!.

Português (Brasil)
Falha ao converter o %1! valor '%2!' para o tipo de dados %3!.

繁體中文
將 %1! 值 '%2!' 轉換成資料類型 %3! 時,轉換失敗。

한국어
%1! 값 '%2!'을(를) 데이터 형식 %3!(으)로 변환하지 못했습니다.

简体中文
在将 %1! 值 '%2!' 转换成数据类型 %3! 时失败。



Contributed by: --SQLDenis 01:01, 19 June 2008 (GMT)

Part of SQL Server Programming Hacks, Section Pitfalls

Part of Common SQL Server Errors, Section Integer Data Types

411 Rating: 2.2/5 (85 votes cast)