If you’re getting error msg 235 which reads Cannot convert a char value to money. The char value has incorrect syntax, it’s probably because you’re trying to convert a string to the money data type, but the string isn’t in a format that can be converted to the money type.

To fix this issue, make sure you’re trying to convert the right value (perhaps you’ve got the wrong column or variable). If you’re sure you’re trying to convert the right value, try a workaround like the one below.

Example of Error

Here’s an example of code that results in the error:

SELECT CAST('-127,54 €' AS money);

Result:

Msg 235, Level 16, State 0, Line 1
Cannot convert a char value to money. The char value has incorrect syntax.

This error occurred because I tried to use the CAST() function to convert a string to money, but the string isn’t in a format that can be converted to money with CAST().

Solution 1

One option is to use the PARSE() function to try to parse the value:

SELECT PARSE('-127,54 €' AS money USING 'fr-FR');

Result:

-127.54

In this case, I also specified the locale. I did this because the input value was provided in the currency format for France. My system uses us_english, and the output reflects that. A different locale may be needed if your input value is in a different format and you know that it’s formatted based on a different locale.

We can use the sys.dm_exec_describe_first_result_set function to check that the value has been converted to money:

SELECT system_type_name
FROM sys.dm_exec_describe_first_result_set(
    'SELECT PARSE(''-127,54 €'' AS money USING ''fr-FR'')', 
    null, 
    0);

Result:

money

Solution 2

Another option is to modify the value so that it’s in a format that can be converted.

Example

SELECT CAST('-127,54' AS money);

Result:

-12754.00

However, changing the input value is fraught with danger. Notice that the result in this solution is different to the result in the first solution (the first solution returns -127.54, but this solution returns -12754.00). That’s because in the first solution, we recognised the input value as being formatted using the currency format for France. In this case, the comma is actually the decimal separator, not the thousands separator.

If the input value is indeed formatted for France, then this second solution returns the wrong value.

Anyway, let’s verify the output data type:

SELECT system_type_name
FROM sys.dm_exec_describe_first_result_set(
    'SELECT CAST(''-127,54'' AS money);', 
    null, 
    0);

Result:

money