0

I am querying a database in which I am getting the following exception:

Conversion failed when converting varchar value 'B6UJ978023EC' to data type int

How can I fix it?

Marek Grzenkowicz
  • 17,024
  • 9
  • 81
  • 111
RHM
  • 351
  • 2
  • 7
  • 20
  • 4
    What is the query you are running? – ChrisBint Aug 01 '12 at 09:26
  • You are probably trying to concatenate varchars and ints, in which case Sql Server will try to convert varchars to int. Use `convert` yourself to prevent it. – Nikola Markovinović Aug 01 '12 at 09:27
  • -1. "My guess is ..." and "you are probably ..." should make it clear to you that you haven't provided enough information for the question to be answerable precisely. The current form of your question sounds much like "I am writing a program and I get the error message 'xyz is not defined as a variable or parameter'. How can I fix it ?". – Erwin Smout Aug 01 '12 at 14:05

2 Answers2

3

My guess is that your query looks something like this:

select * from mytable where SomeField = 'B6UJ978023EC'

If that's the case, SomeField was declared as an INT datatype and Sql Server is attempting to convert 'B6UJ978023EC' to an INT which won't work.

It's possible that you're trying to do an insert/update, so this wouldn't work either if SomeField is an INT.

update mytable
  set SomeField = 'B6UJ978023EC'
where something = something

insert mytable (field1, field2, SomeField)
values(123, 'abc', 'B6UJ978023EC')

Same result, Conversion failed when converting varchar value 'B6UJ978023EC' to data type int

In this case, either change the datatype of the column, or change the value of the variable.

Update:

If your column's datatype is (N)VARCHAR but contains some real numbers, then you can simply add single quotes around the number you're looking for.

-- works
select * from mytable where SomeField = '123456'

-- doesn't work - same error
select * from mytable where SomeField = 123456
Chris Gessler
  • 22,727
  • 7
  • 57
  • 83
  • It could also be the other way round: if `SomeField` is a `varchar`/`nvarchar` field, you get the same error if you do `select * from mytable where SomeField = 1` (search for a numeric value) and there's at least one row in the table that has a non-numeric value. – Christian Specht Aug 01 '12 at 09:44
  • what can be the solution ? to this – RHM Aug 01 '12 at 10:54
  • Quote: "If your column's datatype is (N)VARCHAR but contains some real numbers, then you can simply add single quotes around the number you're looking for.". Thank you, struggled with this for months. – Marco Marsala Nov 25 '15 at 08:40
1

try this:

Error is because you are trying to convert a character value to integer value. Just add the below condition to the query so that it will select only integer values

where  ISNUMERIC(col+ '.0e0')=1
Joe G Joseph
  • 23,518
  • 5
  • 56
  • 58
  • What if the rest of the query contains a calculated value that should also include the rows you are excluding with this. I would fix the problem, not work around it. – ChrisBint Aug 01 '12 at 09:35
  • @shatrughna. No, it will exclude rows where potentially only _some_ of the data is incorrect. For example, this is your timesheet and you are getting paid for each hour that is calculated from the rows, but one of these rows has incorrect data in and is now being excluded from the calculation. Is that the right solution? No. Fix the problem (incorrect data), do not work around it. – ChrisBint Aug 01 '12 at 09:39
  • This will not always work because query optimizer is free to choose the order of evaluation on its own. [Please check accepted answer to this question](http://stackoverflow.com/questions/8896728/isnumeric-in-sql-server-join) – Nikola Markovinović Aug 01 '12 at 09:39
  • @ChrisBint: I agree with you.. This is just to avoid the error and he can see the result set. I dont think there is any other way to display the result while casting a varchar value to integer – Joe G Joseph Aug 01 '12 at 09:40