Ошибка арифметического переполнения при преобразовании expression к типу данных int mssql - Ремонт и установка крупной бытовой техники

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 2000000000 * 3 
--Ошибка арифметического переполнения при преобразовании expression к типу данных int.

SELECT 3000000000 * 3  
-- 9000000000

Если создать вью

create view ts
as 

SELECT 2000000000 * 3 AS A, 3000000000 * 3  AS B

, то в типах данных увидим такое

Источник

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.

Источник

When I run this command with SUM()

SELECT COUNT(*) AS [Records], SUM(t.Amount) AS [Total]
FROM   dbo.t1 AS t
WHERE  t.Id > 0
       AND t.Id < 101;

I’m getting,

Arithmetic overflow error converting expression to data type int.

Any idea on what is the cause of it?

I’m just following the instructions in this answer.

asked May 23, 2017 at 16:55

Evan CarrollEvan Carroll

60.6k44 gold badges224 silver badges457 bronze badges

For values larger than the INT max (2,147,483,647), you’ll want to use COUNT_BIG(*).

SELECT COUNT_BIG(*) AS [Records], SUM(t.Amount) AS [Total]
FROM   dbo.t1 AS t
WHERE  t.Id > 0
       AND t.Id < 101;

If it’s happening in the SUM, you need to convert Amount to a BIGINT.

SELECT COUNT(*) AS [Records], SUM(CONVERT(BIGINT, t.Amount)) AS [Total]
FROM   dbo.t1 AS t
WHERE  t.Id > 0
       AND t.Id < 101;

answered May 23, 2017 at 16:56

Erik DarlingErik Darling

37.6k14 gold badges128 silver badges402 bronze badges

Источник

Remove From My Forums

Question
I have somewhere inside long query with many CTE the following scenario:
```
DECLARE @q_stock INT, @q_sum_all INT, @q_sum int, @q_nar INT
--all this values are calculated before inside some CTE and I guess they are int type

SET @q_stock=97000
SET @q_sum_all=136500
SET @q_sum=136500
SET @q_nar=40000
SELECT ((@q_stock-@q_sum_all+@q_sum)*@q_nar)/CAST(@q_sum as float)
--the problem is in this part:
SELECT (@q_stock-@q_sum_all+@q_sum)*@q_nar
```
Arithmetic overflow error converting expression to data type int.

Why this error is happened? Why sql doesn’t convert it to appropriate dataType if value is greater than INT since I didn’t cast it explicitly?

If I do this there is still error:
```
SELECT CAST((@q_stock-@q_sum_all+@q_sum) *@q_nar AS BIGINT)
```
But if I do this, it is ok:
```
SELECT CAST((@q_stock-@q_sum_all+@q_sum) AS BIGINT)*@q_nar
```
I have multiplication of 2 integers in many of my queires. Should i go over all my queries and convert to BIGINT value before multiply? It would take me days since I need to test everything after change.

Answers

I have 2 options, change operation order or use bigInt:
```
DECLARE @q_sum int, @q_nar INT, @qAll INT

SET @q_sum=136500
SET @q_nar=40000
SET @qAll=97000


1.SELECT CAST(@qAll AS BIGINT)*@q_nar/CAST(@q_sum AS FLOAT)

2.SELECT @qAll*(@q_nar/CAST(@q_sum AS FLOAT))

3.SELECT CAST(@qAll AS BIGINT)*@q_nar/@q_sum

4.SELECT @qAll*(@q_nar/@q_sum)--0
```
In case 4 the result is 0 — so changing operation order on some existing code is very dangerous. I must do test everywhere.

When just cast one operand to BigInt doesn’t affect anything, since end result(after division) is always appropriate for INT type. I don’t need tests in this case I guess.

Or do you see some situation where i could have problems with bigInt?
- Marked as answer by
  
  Friday, May 13, 2016 2:47 PM

Источник

Example of the Error

The Solution

Same Error in Different Scenarios

Question

Answers