// SQL Server error reference

SQL Server error 245: Conversion failed converting a value

SQL Server tried to convert a string to a number (or another type) and the string isn't a valid value of that type, often through implicit conversion.

Error number
245
Severity
16
Category
Data conversion and truncation
Error message
Conversion failed when converting the %ls value '%.*ls' to data type %ls.

What it means

When an expression mixes types, SQL Server converts the lower-precedence type to the higher one. Numbers outrank strings, so comparing a varchar column with an int converts every string to int. If any value isn't a valid number, the query fails with 245 and shows the value, for example Conversion failed when converting the varchar value 'N/A' to data type int.

Common causes

  • Comparing or joining a character column to a numeric value or column: WHERE Code = 42 on a varchar column containing non-numeric codes.
  • Building a string with + and a number, which tries to convert the string to a number: 'Total: ' + @Count.
  • A CASE expression whose branches return both strings and numbers.
  • Bad data in a column that is supposed to hold numbers but is typed as text.

How to fix it

  1. Compare like with like: quote the literal or convert the number to a string.

    SELECT * FROM dbo.Products WHERE Code = '42';
  2. Concatenate with CONCAT, which converts everything to strings, or convert explicitly.

    SELECT CONCAT('Total: ', @Count);
    SELECT 'Total: ' + CAST(@Count AS varchar(20));
  3. Find the values that won't convert with TRY_CONVERT, which returns NULL instead of failing.

    SELECT Code FROM dbo.Products WHERE TRY_CONVERT(int, Code) IS NULL AND Code IS NOT NULL;
  4. Make every branch of a CASE return the same type.

Reproduce it safely

SELECT 'N/A' + 1;  -- Msg 245: Conversion failed when converting the varchar value 'N/A' to data type int.

Related errors

More data conversion and truncation errors

The code and message text are from the Microsoft Database Engine events and errors reference. Browse the full SQL Server error list or search every database error.

// fewer errors, faster fixes

Work on SQL Server in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQL Server and the other databases you use.