Conversion Failed When Converting the varchar Value to Data Type int (Error 245)

The message names the exact value that broke, which is the useful part. The part that catches people is why SQL Server tried to convert that column at all, when the query never asked it to.

Precedence Decides Which Side Converts

When you compare or combine two different types, SQL Server converts the lower precedence type to the higher one. int outranks varchar. So this:

SELECT * FROM dbo.Tickets WHERE Reference = 1234;

does not compare a string to a string. It converts every Reference value it evaluates to int, and the first one containing N/A or a space or a dash fails the whole statement. Quote the literal and the conversion never happens:

SELECT * FROM dbo.Tickets WHERE Reference = '1234';

That one character is the entire fix in a surprising number of cases, and it usually makes the query faster as well, because converting the column side makes the predicate non-sargable and prevents an index seek.

Find the Rows That Cannot Convert

SELECT Reference
FROM   dbo.Tickets
WHERE  TRY_CONVERT(int, Reference) IS NULL
  AND  Reference IS NOT NULL;

TRY_CONVERT returns NULL instead of raising, so it is safe to run over the whole table. That query is the diagnosis, and it is usually a handful of rows carrying N/A, an empty string, a thousands separator, or a value with a trailing space.

Do Not Use ISNUMERIC for This

ISNUMERIC is the traditional answer and it is wrong often enough to matter. It returns 1 for values that will not convert to int:

SELECT ISNUMERIC('1e5')  AS scientific,   -- 1, but not an int
       ISNUMERIC('1.5')  AS decimal_pt,   -- 1, but not an int
       ISNUMERIC('$10')  AS currency,     -- 1, but not an int
       ISNUMERIC('1,000') AS thousands;   -- 1, but not an int

All four pass ISNUMERIC and all four fail a conversion to int. It answers “could this be some numeric type” and you asked “is this an int”. Use TRY_CONVERT or TRY_CAST, which answer the question you actually have.

Why a WHERE Clause Does Not Protect You

This is the one that produces intermittent failures, and it is the most valuable thing on this page.

SELECT CAST(Reference AS int)
FROM   dbo.Tickets
WHERE  IsNumericFlag = 1;          -- looks safe, is not guaranteed

SQL Server does not promise to evaluate the filter before the expression in the select list. The optimiser is free to compute the conversion first, on rows the WHERE was meant to exclude. The query can work for months, then fail after a statistics update or a plan change, with no code deployment to blame.

The reliable forms are the ones where the guard is part of the expression:

SELECT TRY_CONVERT(int, Reference) AS ReferenceInt
FROM   dbo.Tickets;

SELECT CASE WHEN Reference NOT LIKE '%[^0-9]%' AND Reference <> ''
            THEN CAST(Reference AS int) END AS ReferenceInt
FROM   dbo.Tickets;

TRY_CONVERT is the one to reach for. The CASE form is worth knowing for versions before 2012, and the double negative in that LIKE is deliberate: it means “contains no non-digit character”.

The Real Fix Is Usually the Column

A column holding order references as varchar because three rows once said N/A is a data modelling decision that keeps costing. Once the offending rows are cleaned, change the type and the whole class of error disappears, along with the implicit conversions that were quietly stopping index seeks across every query touching it.

Related Scripts

• Get Implicit Conversions, the same type mismatch showing up as a performance problem instead of an error
• String or Binary Data Would Be Truncated, the other everyday data-shape error
• Get Query Performance Deep-Dive, find which query or procedure is raising it in a live workload

Common Questions

Related Scripts

• Get Implicit Conversions, the same type mismatch showing up as a performance problem instead of an error
• String or Binary Data Would Be Truncated, the other everyday data-shape error
• Get Query Performance Deep-Dive, find which query or procedure is raising it in a live workload