Arithmetic overflow error converting expression to data type int.

[Hulikal]

Hi,
I using the following query

select dateadd(s, 1185255439727, '01-01-1970 05:30:00')

on MSSQL 2005. I get the error

Arithmetic overflow error converting expression to data type int.

If I use the query

select dateadd(ss, 1088728808 , '01-01-1970 05:30:00') I dont get this error. Looks big numbers are having problem. Please help

[ck9663]

Hi,
I using the following query

select dateadd(s, 1185255439727, '01-01-1970 05:30:00')

on MSSQL 2005. I get the error

Arithmetic overflow error converting expression to data type int.

If I use the query

select dateadd(ss, 1088728808 , '01-01-1970 05:30:00') I dont get this error. Looks big numbers are having problem. Please help

maybe you should consider adding year instead of seconds on your date...that's a lot of seconds

[jwilliamsoh]

I would take the number of seconds, divide it out;


There are 31536000 Seconds in a year so store that value in a variable as 1 or 2, ect depending upon how many years you have.

Subtract 31536000 * (the value stored from above) from your starting value to get the remaining time. Then do the same for days, then minutes, then seconds.

Do not do this for months as each month has varying number of days.

This way you will date add to Year, Days, Minutes, Seconds.

This will be more manageable.