Sql Server: Arithmetic overflow error converting expression to data type int

Isolate the problem

You can isolate the overflowing portion of your query by gradually commenting out uncertainty areas:

--SELECT SUM(billableDuration) FROM...
SELECT billableDuration FROM yourTable -- Just trying to catch the naughty overflow culprit!


This enables you to detect if billableDuration is the actual villain of the overflow story.

Efficiently casting and computing

Overflow errors can be dealt with by effectively casting numeric values to a higher capacity type. BIGINT is often a viable solution for larger totals:

SELECT CAST(SUM(billableDuration) AS BIGINT) FROM yourTable


In cases where exact decimal operations are required, you might want to switch to NUMERIC(12, 0):

SELECT SUM(CAST(billableDuration AS NUMERIC(12, 0))) FROM yourTable


Working with large datasets

When dealing with large datasets, the count command (COUNT) might overflow. To play safe, use COUNT_BIG instead:

SELECT COUNT_BIG(*) FROM yourTable -- COUNT_BIG isn't scared of the skip counting challenge!


Remember to verify if the resulting data type of SUM expressions aligns with your expectations.

Date and Time: Handle with care

In cases where you're working with dateTimeStamp or there are date-specific computations, always double-check whether the target data type can shelter the output without hassle. Casting to BIGINT might be the way to go for substantial time differences:

SELECT CAST(DATEDIFF(SECOND, MIN(dateTimeStamp), MAX(dateTimeStamp)) AS BIGINT) FROM yourTable


NULL: The unseen enemy

An unhandled NULL can lead to overflow as well. Make sure the appropriate cast or default is in place to deal with NULLs:

-- Since billableDuration can have NULLs
SELECT ISNULL(SUM(CAST(billableDuration AS BIGINT)), 0) FROM yourTable


Test effectively and troubleshoot efficiently

Investigate the behavior of variables by using SQL variables:

DECLARE @Total BIGINT;
SELECT @Total = SUM(CAST(billableDuration AS BIGINT)) FROM yourTable;
PRINT @Total;


Alternatively, try-catch blocks can be used to catch errors smoothly:

BEGIN TRY
    SELECT SUM(CAST(billableDuration AS BIGINT)) FROM yourTable
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE(); --ERROR_MESSAGE: "Hey! Something went wrong. Let's figure it out!"
END CATCH


Proactive measures to avoid overflow

From the get-go, ensure your schema uses suitable data types that can host large data without breaking a sweat. It's always wise to regularly review your schema for signs of potential overflows.

SELECT
    COLUMNPROPERTY(OBJECT_ID('yourTable'), 'YourColumnName', 'Precision'),
    COLUMNPROPERTY(OBJECT_ID('yourTable'), 'YourColumnName', 'Scale')
FROM yourTable
