If you’re getting SQL Server error Msg 8114 that reads something like Error converting data type varchar to numeric, it’s probably because you’re trying to perform a data type conversion that fails due to the value not being able to be converted to the destination type.

It’s not because you can’t convert that type to the new type. It’s because of the value itself.

Example of Error

Here’s an example of code that produces the error:

SELECT CAST('Ten' AS DECIMAL(5,2));

Result:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

This error occurred because SQL Server couldn’t convert the string value to decimal.

Solution 1

To fix this, you’d need to make sure you provide a value that SQL Server can convert.

If you’re passing a column, check that you’ve got the right column. Same if you’re passing a variable – check that it’s the right variable.

Bear in mind that this might not happen in all cases of trying to convert a string to decimal, because some string values can be converted.

For example, the following conversion succeeds:

SELECT CAST('10' AS DECIMAL(5,2));

Result:

10.00

Here, SQL Server was able to work out that 10 is a number, and therefore the conversion succeeded.

Solution 2

If you don’t mind the conversion failing, but you just don’t want it to return an error, try the TRY_CAST() or TRY_CONVERT() functions.

Rather than return an error, these functions return NULL when the value can’t be converted.

Example:

SELECT TRY_CAST('Ten' AS DECIMAL(5,2));

Result:

NULL