If you’re getting error msg 220 that reads something like Arithmetic overflow error for data type…, it’s probably because you’re trying to convert a value to a data type that can’t handle that value. For example, trying to convert a number to a smallint but the number’s too big to fit into a smallint.

To fix this issue, make sure you convert the value to a data type that can handle the size of the number that you’re trying to convert.

Example of Error

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

DECLARE @bank_balance smallmoney = 40000;
DECLARE @wallet smallint = @bank_balance;

Result:

Msg 220, Level 16, State 5, Line 2
Arithmetic overflow error for data type smallint, value = 40000.

This error occurred because I tried to convert a smallmoney value of 40000 to a smallint type. The smallint type can only handle values in the range -32,768 to 32,767, which is lower than the value that I passed, and so we got the error.

We get the same error when doing an explicit conversion:

DECLARE @bank_balance smallmoney = 40000;
SELECT CAST( @bank_balance AS smallint );

Result:

Msg 220, Level 16, State 5, Line 2
Arithmetic overflow error for data type smallint, value = 40000.

Solution

To fix the issue, we should convert to a data type that can handle the number. In our case, we can convert to an int type:

DECLARE @bank_balance smallmoney = 40000;
DECLARE @wallet int = @bank_balance;

Result:

Commands completed successfully.

And for the explicit conversion:

DECLARE @bank_balance smallmoney = 40000;
SELECT CAST( @bank_balance AS int );

Result:

40000

We can verify the type with the sys.dm_exec_describe_first_result_set function:

SELECT system_type_name
FROM sys.dm_exec_describe_first_result_set(
    'DECLARE @bank_balance smallmoney = 40000;
SELECT CAST( @bank_balance AS int )', 
    null, 
    0);

Result:

int