SQL: Using DATEADD with bigints
sql, sql-server-2008
Solution
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
Problem
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.