sql server 2008 - SQL: Using DATEADD with bigints

Question

I have some SQL to convert javascript dates to SQL dates which works great. However, I've encoutered some data which is too large and is causing an exception:

Arithmetic overflow error converting expression to data type int

Here is the SQL in question:

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

I am running this on SQL Server 2008.

Answer 1

Just do the problematic DATEADD in two steps, starting with a coarser time unit (seconds, minutes, hours etc.), then dropping back to the fine grained one for the remainder.

Avoid going to the level of weeks and months though as that would require actual calendar calculations and we would prefer the system to handle that.

Example below needs to calculate a start time given a (possibly) large current duration in milliseconds.

-- large durations can overflow the integer argument needed for DATEADD

-- so do as two steps subtracting minutes (60000ms) and then remaining milliseconds.

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