SQL Server Error Messages - Msg 293

Error Message

Server: Msg 293, Level 16, State 0, Line 1
Cannot convert a char value to smallmoney.  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 smallmoney data type and the character value has invalid characters that cannot be converted to smallmoney data type.

The simplest way of generating this error is converting a non-numeric string into smallmoney data type:

SELECT CAST('ABC' AS SMALLMONEY)

Msg 293, Level 16, State 0, Line 1
Cannot convert char value to smallmoney. 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 smallmoney or even the comma digit grouping separator, will not generate this error:

SELECT CAST('$ 1,000.00' AS SMALLMONEY) AS [DollarValue]
SELECT CAST('€ 1,000.00' AS SMALLMONEY) 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 smallmoney.

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 smallmoney uses a comma as the decimal symbol and the period as the digit grouping symbol:

SELECT CAST('$ 1.000,00' AS SMALLMONEY)

Msg 293, Level 16, State 0, Line 1
Cannot convert char value to smallmoney. 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 smallmoney is of numeric data type. If the string value is of numeric data type, then convert it to smallmoney data type, otherwise, return a NULL value.

DECLARE @SmallMoneyString VARCHAR(20)

SET @SmallMoneyString = '$ 1.000,00'
SELECT CAST(CASE WHEN ISNUMERIC(@SmallMoneyString) = 1
                 THEN @SmallMoneyString
                 ELSE NULL END AS SMALLMONEY)

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 SMALLMONEY) 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 @SmallMoneyString VARCHAR(20)

SET @SmallMoneyString = '$ 1.000,00'
SET @SmallMoneyString = REPLACE(REPLACE(@SmallMoneyString, '.', ''), ',', '.')
SELECT CAST(CASE WHEN ISNUMERIC(@SmallMoneyString) = 1
                 THEN @SmallMoneyString
                 ELSE NULL END AS SMALLMONEY)