|
|
Error Message
Server: Msg 235, Level 16, State 0, Line 1
Cannot convert a char value to money. The char value
has incorrect syntax.
Causes
As the error message suggests, this error happens when converting a character or string value, whether of char, varchar, nchar or nvarchar data type, into a money data type and the character value has invalid characters that cannot be converted to money data type.
|
The simplest way of generating this error is converting a non-numeric string into smallmoney data type:
SELECT CAST('ABC' AS MONEY)
Msg 235, Level 16, State 0, Line 1
Cannot convert char value to money. The char value has incorrect syntax.
Having a currency symbol, such as the dollar sign ($) or the Euro sign (€), in the string value to be converted to money or even the comma digit grouping separator, will not generate this error:
SELECT CAST('$ 1,000.00' AS MONEY) AS [DollarValue]
SELECT CAST('€ 1,000.00' AS MONEY) AS [EuroValue]
DollarValue
-----------
1000.00
EuroValue
-----------
1000.00
The error may be encountered if the decimal symbol and the digit grouping symbol defined in the Regional Options Numbers format on the server where the SQL Server resides differs from the decimal symbol and the digit grouping symbol used in the string value being converted to money.
To illustrate, the decimal symbol defined on the server where my SQL Server resides is the period (.) while the digit grouping symbol is the comma (,). The error may be encountered if the string value being converted to money uses a comma as the decimal symbol and the period as the digit grouping symbol:
SELECT CAST('$ 1.000,00' AS MONEY)
Msg 235, Level 16, State 0, Line 1
Cannot convert char value to money. The char value has incorrect syntax.
Some countries, such as Germany, Italy, Netherlands and Spain, use the comma as the decimal symbol and the period as the digit grouping symbol. Other countries, such as Finland, France, Hungary and Russia, use the comma as the decimal symbol and the space as the digit grouping symbol.
Solution / Workaround:
One way of avoiding this error is to first check whether the string value being converted to money is of numeric data type. If the string value is of numeric data type, then convert it to money data type, otherwise, return a NULL value.
DECLARE @MoneyString VARCHAR(20)
SET @MoneyString = '$ 1.000,00'
SELECT CAST(CASE WHEN ISNUMERIC(@MoneyString) = 1
THEN @MoneyString
ELSE NULL END AS MONEY)
DollarValue
-----------
NULL
Another way of avoiding this error is to replace the decimal symbol and the digit grouping symbol of the string value to match the expected decimal symbol and digit grouping symbol of SQL Server:
SELECT CAST(REPLACE(REPLACE('$ 1.000,00', '.', ''), ',', '.') AS MONEY) AS [DollarValue]
In this solution, the digit grouping symbol is replaced with an empty string. In other words, the digit grouping symbol was removed from the string. Then the next step was to replace the comma decimal symbol with a period.
Yet another way of avoiding this error is the combination of both solutions mentioned above:
DECLARE @MoneyString VARCHAR(20)
SET @MoneyString = '$ 1.000,00'
SET @MoneyString = REPLACE(REPLACE(@MoneyString, '.', ''), ',', '.')
SELECT CAST(CASE WHEN ISNUMERIC(@MoneyString) = 1
THEN @MoneyString
ELSE NULL END AS MONEY)
|