Conversion failed when converting the varchar value to data type int

What it means

A string had to become a number, and one of the values isn’t a number SQL Server can read. The message quotes the value it stopped on and the type it wanted:

Conversion failed when converting the varchar value 'N/A' to data type int.

Often you never wrote CAST. When an expression mixes two types, SQL Server converts the one with the lower data type precedence to the other, and every number type ranks above every string type. So WHERE code = 10 on a varchar column converts each code to int, and 'Order ' + 5 tries to turn 'Order ' into a number.

The conversion runs row by row as the query reads, so some rows may already have reached you when the error stops it. The same problem has other numbers, depending on the target type:

Common causes

1. A text column holding numbers, compared with a number. WHERE code = 10, a join between a varchar and an int column, or a driver sending the value as an integer parameter. One row with N/A, - or 12a fails the query.
2. Building a string with +. 'Order ' + @id where @id is an int.
3. A CASE or COALESCE mixing types. CASE WHEN … THEN 'none' ELSE id END returns int (the higher type), so the 'none' branch fails.
4. Numbers in a format SQL Server doesn’t read: '1,000', '10.5' into an int, '£5', '1,5' with a decimal comma. Spaces around the number are fine, and an empty string becomes 0 for int (but fails for decimal).
5. Filtering out the bad rows and casting in the same query. SQL Server doesn’t promise to apply your WHERE before the CAST, even when the filter is in a subquery, so WHERE code NOT LIKE '%[^0-9]%' AND CAST(code AS int) > 5 can still meet N/A.
6. Dates in a format the session doesn’t expect. '31/12/2026' under the default US English settings, or '12/31/2026' under SET LANGUAGE British, is error 241.

How to fix it

Compare like with like

Quote the value when the column is text, so nothing needs converting:

SELECT id FROM dbo.codes WHERE code = '10';

If the parameter comes from your code, send it as a string type that matches the column.

Find the values that aren’t numbers

TRY_CAST and TRY_CONVERT return NULL instead of failing:

SELECT id, code
FROM dbo.codes
WHERE code IS NOT NULL AND TRY_CAST(code AS int) IS NULL;

Fix or clear those rows, then consider changing the column to a number type.

Use TRY_CAST when bad values are expected

SELECT id, code, TRY_CAST(code AS int) AS as_int
FROM dbo.codes
WHERE TRY_CAST(code AS int) > 5;

Unlike a filter plus CAST, this can’t fail on a non-number, whatever order SQL Server evaluates it in. TRY_CAST still fails for conversions that are never allowed (TRY_CAST(4 AS xml) gives error 529). For numbers with separators, clean them first: TRY_CAST(REPLACE(amount, ',', '') AS decimal(12,2)).

Build strings with CONCAT

CONCAT converts every argument to a string (and treats NULL as empty):

SELECT CONCAT('Order ', 5);            -- Order 5
SELECT 'Order ' + CAST(5 AS varchar(11));

Give every CASE branch the same type

SELECT id, CASE WHEN code = 'N/A' THEN 'none' ELSE CAST(id AS varchar(11)) END AS label
FROM dbo.codes;

Write dates unambiguously

'2026-12-31' is read the same way for date and datetime2 whatever the language; for the older datetime type, use '20261231'. For other formats, give CONVERT a style: 103 is dd/mm/yyyy.

SELECT CONVERT(date, '31/12/2026', 103);   -- 2026-12-31

Reproduce it

On SQL Server 2022 (16.0.4295.3) with sqlcmd, a table codes with a varchar(10) column holding '10', '20', 'N/A' and '7':

SELECT id FROM seo_sqlerr_data.codes WHERE code = 10;

id
--
1
Msg 245, Level 16, State 1, Server 7732422b7f56, Line 1
Conversion failed when converting the varchar value 'N/A' to data type int.

The first row arrived before the error. WHERE code = '10' returned it without one. Both the filter-then-cast forms (in one WHERE, and with the filter in a derived table) returned ids 1 and 2 and then failed the same way on 'N/A'; the TRY_CAST version returned rows without an error. Other forms:

Msg 245, Level 16, State 1, Server 7732422b7f56, Line 1
Conversion failed when converting the varchar value 'Order ' to data type int.
Msg 245, Level 16, State 1, Server 7732422b7f56, Line 1
Conversion failed when converting the varchar value 'none' to data type int.
Msg 8114, Level 16, State 5, Server 7732422b7f56, Line 1
Error converting data type varchar to numeric.
Msg 8114, Level 16, State 5, Server 7732422b7f56, Line 1
Error converting data type varchar to bigint.
Msg 241, Level 16, State 1, Server 7732422b7f56, Line 1
Conversion failed when converting date and/or time from character string.

Those were 'Order ' + 5, the CASE above, CAST('1,5' AS decimal(10,2)), CAST('x1' AS bigint) and CAST('31/12/2026' AS date). CAST('' AS int) returned 0 and CAST(' 12 ' AS int) returned 12. Under SET DATEFORMAT dmy, '2026-12-31' still worked as a date but failed as a datetime with error 242 (The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.); '20261231' worked for both. Under SET LANGUAGE British, Deutsch and Français, '2026-12-31' worked as date and datetime2 but not as datetime. SQL Server 2019 and 2025 printed the same messages.

In Inlet

When SQL Server refuses, Inlet shows its message with SQL Server error 245, state 1, severity 16 and links to this page. A table browsed in Inlet can be filtered on the server, so you can look at the rows of a text column before you change its type. With your own Anthropic API key, Ask Claude (⌘L) can rewrite the query with TRY_CAST; it sends the schema and the query, never rows.