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()))

Answer 2

One way I got around the Integer overflow issue was to subtract a more recent date from the microtime unix time stamp.

DATEADD(s, (CreationTimeStamp/1000-1384128000), '2013-11-11') AS CreateDate,

This will not fix the OP's problem because they will still overflow the max on the date column.

Answer 3

According to MSDN, in DATEADD (datepart , number , date )

number is an expression that can be resolved to an int that is added to a datepart of date. User-defined variables are valid. If you specify a value with a decimal fraction, the fraction is truncated and not rounded.

Also notice that even if you give number as an integer, depending on your date & datepart, it could overflow the max range of the date which is 31-12-9999 for sql server 2008

Number has to be an integer. Here is a Test Demo