Arithematic overflow error converting expression to int

Question

following is my query in sql 2005.But iam getting an error like arithmetic overflow error converting expression to data type int .

I think it is related to dateadd() function.Because when i put a sample date ,this query works well.

This is my query.

select tb_batch.batch_number, dateadd(s, tb_batch.exp_date,'1970-01-01')as exp_date,
tb_batch.purchase_rate, tb_batch.sale_rate from tb_batch

And This is the error

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

Here datatype of exp_date is bigint

pls help me to solve this.

Upendra Pratap Shahi · Answer

DATEADD(interval increment int  date)

Jignesh Trivedi · Answer

Hi syntax of DateAdd is DATEADD(interval increment int  date) This is because of increment element of DateAdd is accept int value and you pass bigint value. instead of doing this first convert second to hour or day and pass hour or day in this function. this will resolved your problem hope this will help you.

nijisha k c · Answer

k.thank you.i will try

Amitesh Verma · Answer

dateadd and cast is suited for datetime data type not for bigint.alter your datacolumn and follow the architecture from next time.

nijisha k c · Answer

these are the values for exp_date in my db.And datatype of exp_date is bigint

Amitesh Verma · Answer

1325376000 1159660800 1454284800 1517443200 1427846400 1249084800 this is not the date mam.. what is these valuesare you using datetime.ticks from your c# application??? whats the reason of making exp_date as bigintuse date as the datatype

nijisha k c · Answer

These are the db values... 1325376000 1159660800 1454284800 1517443200 1427846400 1249084800

Amitesh Verma · Answer

accept it if you found your answer.

nijisha k c · Answer

There is no error in the Both 2 above.But it give me date '2015-08-04 15:44:00' for all rows.

Amitesh Verma · Answer

select tb_batch.batch_number dateadd(s30 cast(tb_batch.exp_date as smalldatetime))as exp_date tb_batch.purchase_rate tb_batch.sale_rate from tb_batch

Amitesh Verma · Answer

try converting your date to smalldatetime using cast.then try it SELECT CAST(GETDATE() AS SMALLDATETIME) if error remains theresend me the tb_batch.exp_date field value

nijisha k c · Answer

Sorry.The error remains there.

Amitesh Verma · Answer

select tb_batch.batch_number dateadd(s30 tb_batch.exp_date)as exp_date tb_batch.purchase_rate tb_batch.sale_rate from tb_batch dateadd generally have syntax like dateadd(intervalincrement smalldate)