How to Avoid DATEADD Overflow Errors When Converting Large JavaScript Dates in SQL Server 2008?

DATEADD Overflow with Large Integers

SQL Server 2008 users may encounter an "Arithmetic overflow error" when converting large JavaScript dates to SQL dates using the DATEADD function. The error occurs when the value exceeds the maximum size of an integer data type.

The offending query:

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

To overcome this issue, break down the DATEADD into smaller increments. Start with a larger time unit (seconds, minutes, or hours) and then adjust by the remaining milliseconds. Avoid using weeks or months as this involves complex calendar calculations.

Consider the following example:

-- Handle large durations by subtracting minutes and then remaining milliseconds.

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

This technique allows for calculation of start times with extremely large durations without overflow errors.

The above is the detailed content of How to Avoid DATEADD Overflow Errors When Converting Large JavaScript Dates in SQL Server 2008?. For more information, please follow other related articles on the PHP Chinese website!