Failed

• Identify and resolve “Conversion failed when converting the varchar value ‘Something’ to data type int”

  Feb 26, 2019

  If you are seeing an error similar to “Conversion failed when converting the varchar value ‘something’ to data type int.”, I find the easiest method for identifying a solution to the problem is to analyze the data causing the error.

  To illustrate how I identify bad data when making conversions, consider the environment setup below:

  DECLARE @table TABLE (id VARCHAR(4));
  INSERT INTO @table (id)
  VALUES ('1' -- id - int
      )
       , ('NULL')
       , (NULL);
  

  We have a simple table with a single column. The column type is a varchar(4) that allows NULL entries. We have inserted a 1, NULL, and the word “NULL”. Here’s what our table looks like: