How to Avoid Arithmetic Overflow Errors When Using DATEADD with Bigints in SQL Server?

Handling Arithmetic Overflow Errors with DATEADD and Bigints

The DATEADD function in SQL Server is a powerful tool for manipulating dates and times. However, it can encounter arithmetic overflow errors when working with big integers.

Problem:

A user attempted to convert a JavaScript date to a SQL date using the following query:

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

However, it resulted in an exception:

Arithmetic overflow error converting expression to data type int

Solution:

To avoid the overflow error, it is recommended to break down the DATEADD operation into smaller steps, using coarser time units like seconds or minutes as the starting point. This allows the system to handle the calendar calculations more efficiently.

For example, the following query will calculate a start time from a millisecond duration:

-- Avoid using weeks or months due to calendar irregularities

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

This approach ensures that arithmetic operations are performed within the limits of the integer data type, preventing overflow errors.

The above is the detailed content of How to Avoid Arithmetic Overflow Errors When Using DATEADD with Bigints in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!