If you’re receiving error Msg 8115, Level 16, Arithmetic overflow error converting expression to data type int in SQL Server, it could be that you’re performing a calculation that results in an out of range value.
This can happen when you use a function such as SUM() on a column, and the calculation results in a value that’s outside the range of the column’s type.
Example of the Error
Here’s an example of code that produces the error:
SELECT SUM(bank_balance)
FROM accounts;Result:
Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expression to data type int.
In this case I used the SUM() function to get the sum of the bank_balance column, which has a data type of int.
The error occurred because the result of the calculation is outside the range of the int data type.
Here’s all the data in my table:
SELECT bank_balance
FROM accounts;Result:
+----------------+ | bank_balance | |----------------| | 1300000000 | | 1200000000 | | 800500000 | +----------------+
Those are some big bank balances… and adding the three of them results in a larger number than an int can handle (the int range is -2,147,483,648 to 2,147,483,647).
The Solution
We can deal with this error by converting the int column to a bigint when we run the query:
SELECT SUM(CAST(bank_balance AS bigint))
FROM Accounts;Result:
3300500000
This time it worked.
You could also change the data type of the actual column for a more permanent solution.
In case you’re wondering, the bigint range is -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
Same Error in Different Scenarios
The same error (Msg 8115) can also occur (with a slightly different error message) when you try to explicitly convert between data types and the original value is outside the range of the new type. See Fix “Arithmetic overflow error converting int to data type numeric” in SQL Server to fix this.
The same error (Msg 8115) can also occur (with a slightly different error message) when you try to insert data into a table when its IDENTITY column has reached its data type’s limit. See Fix: “Arithmetic overflow error converting IDENTITY to data type…” in SQL Server for how to fix this.
Table structure is
userid int
fname varchar(50)
lname varchar(50)
Email varchar(50)
Phone decimal(18,2)
This is procedure
ALTER procedure [dbo].[AddUser]
@userid int,
@fname varchar(50),
@lname varchar(50),
@Email varchar(50),
@Phone decimal(18,2)
as
insert into UserInfo(Userid, Fname, Lname, Email, Phone)
values(@userid, @fname, @lname, @Email, @Phone)
This is my code….
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["con1"]);
try
{
con.Open();
SqlCommand cmd = new SqlCommand("AddUser",con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@userid",Int32.Parse(TextBox1.Text.Trim()));
cmd.Parameters.AddWithValue("@fname",TextBox2.Text) ;
cmd.Parameters.AddWithValue("@lname",TextBox3.Text) ;
cmd.Parameters.AddWithValue("@Email",TextBox4.Text);
cmd.Parameters.AddWithValue("@Phone",TextBox5.Text);
cmd.ExecuteNonQuery();
Label1.Text = "Record Has Been Added Successfully!";
}
catch (Exception en)
{
Label1.Text = "Retry to add";
}
}
If I enter Phone no value max any eg. 233242342423 it gives error… please how to solve it?
Table structure is
userid int
fname varchar(50)
lname varchar(50)
Email varchar(50)
Phone decimal(18,2)
This is procedure
ALTER procedure [dbo].[AddUser]
@userid int,
@fname varchar(50),
@lname varchar(50),
@Email varchar(50),
@Phone decimal(18,2)
as
insert into UserInfo(Userid, Fname, Lname, Email, Phone)
values(@userid, @fname, @lname, @Email, @Phone)
This is my code….
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["con1"]);
try
{
con.Open();
SqlCommand cmd = new SqlCommand("AddUser",con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@userid",Int32.Parse(TextBox1.Text.Trim()));
cmd.Parameters.AddWithValue("@fname",TextBox2.Text) ;
cmd.Parameters.AddWithValue("@lname",TextBox3.Text) ;
cmd.Parameters.AddWithValue("@Email",TextBox4.Text);
cmd.Parameters.AddWithValue("@Phone",TextBox5.Text);
cmd.ExecuteNonQuery();
Label1.Text = "Record Has Been Added Successfully!";
}
catch (Exception en)
{
Label1.Text = "Retry to add";
}
}
If I enter Phone no value max any eg. 233242342423 it gives error… please how to solve it?
I’m getting this error
msg 8115, level 16, state 2, line 18
Arithmetic overflow error converting expression to data type int.
with this SQL query
DECLARE @year VARCHAR(4);
DECLARE @month VARCHAR(2);
-- START OF CONFIGURATION SECTION
-- THIS IS THE ONLY SECTION THAT SHOULD BE MODIFIED
-- SET THE YEAR AND MONTH PARAMETERS
SET @year = '2013';
SET @month = '3'; -- 1 = January.... 12 = Decemeber.
-- END OF CONFIGURATION SECTION
DECLARE @startDate DATE
DECLARE @endDate DATE
SET @startDate = @year + '-' + @month + '-01 00:00:00';
SET @endDate = DATEADD(MONTH, 1, @startDate);
SELECT
DATEPART(YEAR, dateTimeStamp) AS [Year]
, DATEPART(MONTH, dateTimeStamp) AS [Month]
, COUNT(*) AS NumStreams
, [platform] AS [Platform]
, deliverableName AS [Deliverable Name]
, SUM(billableDuration) AS NumSecondsDelivered
FROM
DeliveryTransactions
WHERE
dateTimeStamp >= @startDate
AND dateTimeStamp < @endDate
GROUP BY
DATEPART(YEAR, dateTimeStamp)
, DATEPART(MONTH, dateTimeStamp)
, [platform]
, deliverableName
ORDER BY
[platform]
, DATEPART(YEAR, dateTimeStamp)
, DATEPART(MONTH, dateTimeStamp)
, deliverableName
SELECT
DATEPART(YEAR, dateTimeStamp) AS [Year]
, DATEPART(MONTH, dateTimeStamp) AS [Month]
, COUNT(*) AS NumStreams
, [platform] AS [Platform]
, deliverableName AS [Deliverable Name]
, SUM(billableDuration) AS NumSecondsDelivered
Предполагая, что ваш цитируемый текст является точным текстом, один из этих столбцов не может выполнять математические вычисления, которые вам нужны. Дважды щелкните ошибку, и она выделит строку, которая вызывает проблемы (если она отличается от опубликованной, ее может не быть); Я проверил ваш код с переменными, и проблем не возникло, а это означает, что один из этих столбцов (о котором мы не знаем более конкретной информации) создает эту ошибку.
Одно из ваших выражений должно быть приведено/преобразовано в int, чтобы это произошло, что является значением Arithmetic overflow error converting expression to data type int.