Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : SQL Server Tutorial - UPDATE Statement 101
Error Messages
Home > SQL Server Error Messages > Msg 294 - The conversion from char data type to smallmoney data type resulted in a smallmoney overflow error.
SQL Server Error Messages - Msg 294 - The conversion from char data type to smallmoney data type resulted in a smallmoney overflow error.

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.

Related Articles :