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
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 = 42on avarcharcolumn containing non-numeric codes. - Building a string with
+and a number, which tries to convert the string to a number:'Total: ' + @Count. - A
CASEexpression 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
-
Compare like with like: quote the literal or convert the number to a string.
SELECT * FROM dbo.Products WHERE Code = '42';
-
Concatenate with
CONCAT, which converts everything to strings, or convert explicitly.SELECT CONCAT('Total: ', @Count); SELECT 'Total: ' + CAST(@Count AS varchar(20)); -
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;
-
Make every branch of a
CASEreturn 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
8114Error converting data type241Conversion failed for date and/or time8115Arithmetic overflow error converting22P02PostgreSQL: invalid input syntax for type1366MySQL: Incorrect value for columnORA-01722Oracle: invalid number
More data conversion and truncation errors
8152String or binary data truncated2628Truncated in table, column242Out-of-range date conversion512Subquery returned more than 1 value8134Divide by zero error encountered220Arithmetic overflow error for data type248Conversion overflowed an int column8169Conversion to uniqueidentifier failed
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.
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.