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.

Microsoft’s reference covers data type precedence, the full ranking of 31 types with int at 17 and varchar at 28, and TRY_CONVERT in full.

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.

When the message reaches you from an application and you cannot see the statement, capture it at the moment it fails. Msg 245 is not written to the error log, but an Extended Events session filtered to the error number records the SQL text, the database, the login and the application that raised it:

CREATE EVENT SESSION [conversion_245] ON SERVER
ADD EVENT sqlserver.error_reported
(
    ACTION (sqlserver.sql_text, sqlserver.database_name, sqlserver.client_app_name, sqlserver.username)
    WHERE error_number = 245
)
ADD TARGET package0.ring_buffer;
ALTER EVENT SESSION [conversion_245] ON SERVER STATE = START;

Reproduce the failure, then read the ring buffer. The result is one XML document; click it in SSMS and each event node carries the statement text and the message, offending value included. Drop the session once you have the statement, it is a diagnostic rather than a monitor:

SELECT  CAST(t.target_data AS xml) AS ring_buffer
FROM    sys.dm_xe_sessions AS s
JOIN    sys.dm_xe_session_targets AS t ON t.event_session_address = s.address
WHERE   s.name = 'conversion_245';

DROP EVENT SESSION [conversion_245] ON SERVER;

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.

Common Questions

Related Scripts

• Get Implicit Conversions, the same type mismatch showing up as a performance problem instead of an error
• How to Search for a String in All Tables in SQL Server, when the text that failed conversion may be stored in an unknown table
• String or Binary Data Would Be Truncated, the other everyday data-shape error
• Get Query Performance Deep-Dive, once the Extended Events session has named the procedure, its plan-cache history and variance