|
|
Error Message
Msg 294, Level 16, State 0, Line 1
The conversion from char data type to smallmoney
data type resulted in a smallmoney overflow error.
Causes
This error occurs when converting a CHAR, VARCHAR, NCHAR or NVARCHAR data type into a SMALLMONEY data type using either the CAST or CONVERT functions but the value being converted is larger than the maximum value that a SMALLMONEY data type can handle or smaller than the minimum value that a SMALLMONEY data type can handle.
|
To illustrate, the following SELECT statement will generate the error:
SELECT CAST('1000000.00' AS SMALLMONEY)
Msg 294, Level 16, State 0, Line 1
The conversion from char data type to smallmoney data type resulted in a smallmoney overflow error.
The error occurred because the maximum value that a SMALLMONEY data type can handle is only 214,748.3647.
Similarly, the following SELECT statement will also generate the error:
SELECT CAST('-1000000.00' AS SMALLMONEY)
Msg 294, Level 16, State 0, Line 1
The conversion from char data type to smallmoney data type resulted in a smallmoney overflow error.
The error occurred because the minimum value that a SMALLMONEY data type can handle is only -214,748.3648.
Solution / Workaround:
To avoid or overcome this error, use a different data type that can handle the value being converted from the character data type. Instead of using a SMALLMONEY data type, use a MONEY data type when converting a character data type value greater than 214,748.3647 or less than -214,748.3648.
SELECT CAST('1000000.00' AS MONEY)
SELECT CAST('-1000000.00' AS MONEY)
The MONEY data type can handle a value from -922,337,203,286,477.5808 up to +922,337,203,286,477.5807. One drawback of using a MONEY data type is that it uses up 8 bytes, compared to 4 bytes used by SMALLMONEY data type. If the monetary values to be stored will not contain any decimal places (or cents), instead of using MONEY data type, the INT data type can be used instead. The INT data type only uses up 4 bytes just like the SMALLMONEY data type but unlike the SMALLMONEY data type, the INT data type can handle values from -2,147,483,648 and 2,147,483,647.
|