SQL Server:将表达式转换为数据类型 int 时出现算术溢出错误 [英] SQL Server : Arithmetic overflow error converting expression to data type int

问题描述

我收到此错误

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

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

使用此 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   

推荐答案

SUM(billableDuration) 有问题吗?要找出答案，请尝试注释掉该行并查看它是否有效.

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

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

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

这篇关于SQL Server:将表达式转换为数据类型 int 时出现算术溢出错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！