SQL Server: 'Conversion failed when converting the varchar value to data type int' (error 245)

Conversion failed when converting the varchar value 'abc' to data type int. (error 245) means SQL Server had to turn a string into an int and the string was not a number. Mixing a string with an int column makes SQL Server convert the string, because int ranks above varchar. Send the value as a typed int parameter, checked in your code first, and the server never sees 'abc'.

In C#, parse with int.TryParse and add the parameter as SqlDbType.Int. When the bad strings are in the table itself, compare as strings or use TRY_CAST.

The error

PS> .\FixLab.exe 'Server=(localdb)\MSSQLLocalDB;Database=fix6_lab;Integrated Security=true' 'SELECT Id, Name FROM dbo.Customers WHERE Id = ''abc'''
ex.Number = 245
Microsoft.Data.SqlClient.SqlException: Conversion failed when converting the varchar value 'abc' to data type int.

PS> .\FixLab.exe 'Server=(localdb)\MSSQLLocalDB;Database=fix6_lab;Integrated Security=true' 'INSERT INTO dbo.Orders (CustomerId, Quantity) VALUES (1, ''abc'')'
ex.Number = 245
Microsoft.Data.SqlClient.SqlException: Conversion failed when converting the varchar value 'abc' to data type int.

Why it happens

When an operator combines two data types, SQL Server converts the one with lower data type precedence to the higher one. int sits above nvarchar and varchar, so in Id = 'abc' the literal is converted to int, and the conversion fails. Id = '2' runs fine, which is why the bug hides until someone types a letter. The INSERT fails for the same reason: the target column is int. sqlcmd and SSMS print it as Msg 245, Level 16, State 1.

The direction matters. With a varchar column and a number, the column is converted, row by row, and the value in the message comes from your data, not from your query:

PS> .\FixLab.exe 'Server=(localdb)\MSSQLLocalDB;Database=fix6_lab;Integrated Security=true' 'SELECT Id, Name FROM dbo.Customers WHERE Phone = 5550101'
2 | David Chen
ex.Number = 245
Microsoft.Data.SqlClient.SqlException: Conversion failed when converting the varchar value '+00-555-0100 ext 123' to data type int.

One row came back before the failure. From application code the message often says nvarchar: AddWithValue("@id", input) with a C# string created an NVarChar parameter, and "abc" failed with "converting the nvarchar value 'abc' to data type int".

The fix

if (!int.TryParse(input, out var id))
{
    Console.WriteLine($"'{input}' is not a customer number.");
    return 1;
}
using var connection = new SqlConnection(ConnectionString);
connection.Open();
using var command = new SqlCommand("SELECT Id, Name FROM dbo.Customers WHERE Id = @id", connection);
command.Parameters.Add("@id", SqlDbType.Int).Value = id;

PS> .\FixEf.exe lookup abc
'abc' is not a customer number.

PS> .\FixEf.exe lookup 2
2 | David Chen

The bad input now stops in your code, where it can become a validation message instead of a server error. Passing the raw string to an Int parameter is not enough on its own: SqlClient threw FormatException: Failed to convert parameter value from a String to a Int32. before sending anything. EF Core does the typing for you: a LINQ filter on an int property was logged as @id='?' (DbType = Int32).

For text columns that hold numbers, compare as text (WHERE Phone = '5550101') or use TRY_CAST, which returns NULL instead of failing:

PS> .\FixLab.exe 'Server=(localdb)\MSSQLLocalDB;Database=fix6_lab;Integrated Security=true' 'SELECT Id, Name FROM dbo.Customers WHERE TRY_CAST(Phone AS int) = 5550101'
2 | David Chen
OK (-1 row(s) affected) in 0.0 s

PS> .\FixLab.exe 'Server=(localdb)\MSSQLLocalDB;Database=fix6_lab;Integrated Security=true' 'SELECT Id, Name, Phone FROM dbo.Customers WHERE Phone IS NOT NULL AND TRY_CAST(Phone AS int) IS NULL'
13 | Kwame Mensah | +00-555-0100 ext 123
OK (-1 row(s) affected) in 0.0 s

The second query lists every value that is not a number, which is the list to clean up.

How it was reproduced

Two console apps from dotnet new console -n FixLab -f net10.0 and -n FixEf (.NET SDK 10.0.402): FixLab with Microsoft.Data.SqlClient 7.1.1 runs the SQL in its second argument, and FixEf holds the typed lookup and an EF Core 10.0.13 query. The test database fix6_lab on SQL Server 2022 LocalDB 16.0.1210.3 had dbo.Customers with an INT key and a VARCHAR phone, and dbo.Orders with INT columns; all rows were fictional.

Frequently asked

How do I fix Conversion failed when converting the varchar value to data type int?

Make both sides of the comparison the same type. Send numbers as int parameters checked with int.TryParse, compare text columns with text literals, or use TRY_CAST, which returns NULL for values that are not numbers.

Why does SQL Server convert my varchar column to int?

Data type precedence: when an expression mixes types, the lower one is converted to the higher one, and int ranks above varchar and nvarchar. Comparing a varchar column with a number converts the column's values row by row, and the first non-numeric value stops the query.

Why does the error say nvarchar when my column is varchar?

The string came from a parameter. AddWithValue with a C# string creates an NVarChar parameter, so the message names nvarchar. Add the parameter with SqlDbType.Int and pass an int instead.

Why a phone number belongs in a text column at all is covered in the column types part of Database Design for Developers. More decoded errors in the Fixes category.