SQL Server：将表达式转换为数据类型 int 时出现算术溢出错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15950580/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server : Arithmetic overflow error converting expression to data type int
提问by user2270544
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
回答by Jeff Johnston
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))。
回答by Faiyaz
Change SUM(billableDuration) AS NumSecondsDeliveredto
更改SUM(billableDuration) AS NumSecondsDelivered为
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 as numeric(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 操作可以正常工作。
回答by John G
Very simple:
很简单：
Use COUNT_BIG(*) AS NumStreams
回答by Question3CPO
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 才能通过，这就是Arithmetic overflow error converting expression to data type int.
回答by John Tecle
On my side, this error came from the data type "INT' in the Null values column. The error is resolved by just changing the data a type to varchar.
就我而言，此错误来自 Null 值列中的数据类型“INT”。只需将数据类型更改为 varchar 即可解决该错误。
回答by user7344470
declare @d real
set @d=1.0;
select @d*40000*(192+2)*20000+150000
