Arithematic overflow error converting expression to int

nijisha k c

11y1.5kSQL

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.

13 Replies

Know the answer? Post it — somebody with the same question will find it here.

Upendra Pratap Shahi11y ago
DATEADD(interval, increment int, date)
0
Jignesh Trivedi11y ago
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.

0
nijisha k c11y ago
k.thank you.i will try
0
Amitesh Verma11y ago
dateadd and cast is suited for datetime data type not for bigint.alter your datacolumn and follow the architecture from next time.
0
nijisha k c11y ago
these are the values for exp_date in my db.And datatype of exp_date is bigint
0
Amitesh Verma11y ago
1325376000 1159660800 1454284800 1517443200 1427846400 1249084800

this is not the date mam..
what is these values,are you using datetime.ticks from your c# application???
whats the reason of making exp_date as bigint,use date as the datatype
0
nijisha k c11y ago
These are the db values... 1325376000 1159660800 1454284800 1517443200 1427846400 1249084800
0
Amitesh Verma11y ago
accept it if you found your answer.
0
nijisha k c11y ago
There is no error in the Both 2 above.But it give me date '2015-08-04 15:44:00' for all rows.
0
Amitesh Verma11y ago
select tb_batch.batch_number, dateadd(s,30 ,cast(tb_batch.exp_date as smalldatetime))as exp_date,
tb_batch.purchase_rate, tb_batch.sale_rate from tb_batch
0
Amitesh Verma11y ago
try converting your date to smalldatetime using cast.then try it

SELECT CAST(GETDATE() AS SMALLDATETIME)

if error remains there,send me the tb_batch.exp_date field value
0
nijisha k c11y ago
Sorry.The error remains there.
0
Amitesh Verma11y ago
select tb_batch.batch_number, dateadd(s,30 ,tb_batch.exp_date)as exp_date,
tb_batch.purchase_rate, tb_batch.sale_rate from tb_batch

dateadd generally have syntax like
dateadd(interval,increment ,smalldate)
0