SQL Server DATEADD: How to Avoid Arithmetic Overflow with Large Integer Timestamps?

DATEADD Pitfalls with Large Integers

When working with large integer timestamps, the DATEADD function in SQL Server can encounter limitations. This article explores a specific case where a large integer value causes an arithmetic overflow error.

Problem

Consider the following SQL statement, intended to convert a large JavaScript timestamp into an SQL date:

DATEADD(MILLISECOND, cast(569337307200000 as bigint) % 1000, DATEADD(SECOND, cast(569337307200000 as bigint) / 1000, '19700101'))

On SQL Server 2008, executing this statement results in an "Arithmetic overflow error converting expression to data type int."

Solution

The key to overcoming this error lies in breaking down the DATEADD operation into smaller chunks. By initially adding the time in larger units (e.g., seconds or minutes) and then adding the remaining milliseconds, we can avoid the overflow.

For instance, the following statement calculates the start time given a large duration (in milliseconds):

DATEADD(ms, -large_duration_ms%60000, DATEADD(minute, -large_duration_ms/60000, GETDATE()))

This example subtracts the minutes from the large duration first, then adds the remaining milliseconds. This approach ensures that the intermediate calculations do not overflow.

The above is the detailed content of SQL Server DATEADD: How to Avoid Arithmetic Overflow with Large Integer Timestamps?. For more information, please follow other related articles on the PHP Chinese website!