How to solve error Arithmetic overflow error converting expression to data type int?-CodePudding

When running the following query(I created the query using python):

SELECT x, y, z, sum(r) AS R 
FROM data_table 
WHERE [start_time] >= convert(datetime, '2022-02-03') 
  AND [end_time] <= CONVERT(datetime, '2022-02-07')
GROUP BY x, y, z

I got the error:

Arithmetic overflow error converting expression to data type int.

If I change the date to '2022-02-04' it is working fine. Can someone help me fix my query that it will work in all dates?

CodePudding user response：

seems like the result of sum() overflows, how about cast the r to bigint before sum the result up :

SELECT x, y, z, sum(cast(r as bigint)) AS R 
FROM data_table 
WHERE [start_time] >= convert(datetime, '2022-02-03') 
  AND [end_time] <= CONVERT(datetime, '2022-02-07')
GROUP BY x, y, z

db<>fiddle here