SQL Server：将表达式转换为数据类型int的算术溢出错误

I'm getting this error

我收到了这个错误

msg 8115, level 16, state 2, line 18
Arithmetic overflow error converting expression to data type int.

msg 8115，级别16，状态2，第18行将表达式转换为数据类型int的算术溢出错误。

with this SQL query

使用此SQL查询

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

4 个解决方案

#1

Is the problem with SUM(billableDuration)? To find out, try commenting out that line and see if it works.

SUM（billableDuration）有问题吗？要找到答案，请尝试评论该行，看看它是否有效。

It could be that the sum is exceeding the maximum int. If so, try replacing it with SUM(CAST(billableDuration AS BIGINT)).

可能是总和超过最大int。如果是这样，请尝试将其替换为SUM（CAST（billableDuration AS BIGINT））。

#2

Change SUM(billableDuration) AS NumSecondsDelivered to

更改SUM（billableDuration）AS NumSecondsDelivered to

sum(cast(billableDuration as bigint)) or

sum（cast（billableDuration as bigint））或

sum(cast(billableDuration as numeric(12, 0))) according to your need.

sum（cast（billableDuration为数字（12,0）））根据您的需要。

The resultant type of of Sum expression is the same as the data type used. It throws error at time of overflow. So casting the column to larger capacity data type and then using Sum operation works fine.

Sum表达式的结果类型与使用的数据类型相同。它在溢出时抛出错误。因此，将列转换为更大容量的数据类型然后使用Sum操作可以正常工作。

#3

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

Assuming that your quoted text is the exact text, one of these columns can't do the mathematical calculations that you want. Double click on the error and it will highlight the line that's causing the problems (if it's different than what's posted, it may not be up there); I tested your code with the variables and there was no problem, meaning that one of these columns (which we don't know more specific information about) is creating this error.

假设您的引用文本是确切的文本，其中一列不能进行您想要的数学计算。双击错误，它将突出显示导致问题的行（如果它与发布的不同，它可能不在那里）;我用变量测试了你的代码并且没有问题，这意味着其中一个列（我们不知道更具体的信息）正在创建这个错误。

One of your expressions needs to be casted/converted to an int in order for this to go through, which is the meaning of Arithmetic overflow error converting expression to data type int.

您需要将其中一个表达式转换为/转换为int以使其通过，这就是将表达式转换为数据类型int的算术溢出错误的含义。

#4

-1

declare @d real
set @d=1.0;
select @d*40000*(192+2)*20000+150000

#1