SQL Server error 8114: Error converting data type
SQL Server couldn't convert a value from one data type to another, often a varchar to numeric conversion or a parameter of the wrong type.
- Error number
8114- Severity
- 16
- Category
- Data conversion and truncation
What it means
Error 8114 is a general conversion failure, raised for example when a string can't be converted to numeric or decimal, or when a value passed to a stored procedure parameter doesn't fit the parameter's type. The message names the source and target types (Error converting data type varchar to numeric).
Common causes
- Converting text that isn't a valid number, such as
'1,234.50'or'12abc', todecimalornumeric. - Passing a string to a numeric procedure parameter:
EXEC dbo.SetPrice @Price = 'free'. - Regional formatting: a decimal comma (
'12,5') where SQL Server expects a decimal point.
How to fix it
-
Find the values that don't convert.
SELECT Amount FROM staging.Payments WHERE TRY_CONVERT(decimal(18, 2), Amount) IS NULL AND Amount IS NOT NULL;
-
Clean formatting before converting, or parse with a culture.
SELECT TRY_PARSE('12,5' AS decimal(18, 2) USING 'de-DE'); -
Pass parameters with the right types from the application instead of strings.
Reproduce it safely
SELECT CAST('12abc' AS decimal(10, 2)); -- Msg 8114: Error converting data type varchar to numeric.
Related errors
245Conversion failed converting a value8115Arithmetic 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, column241Conversion failed for date and/or time242Out-of-range date conversion512Subquery returned more than 1 value8134Divide by zero error encountered220Arithmetic overflow error for data type248Conversion overflowed an int column
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.