June 28, 2016 at 7:29 am
Hi Folks,
I am trying to execute this query in a job and I am getting an error Arithmetic overflow error converting expression to data type int. [SQLSTATE 22003] (Error 8115)Unable to open Step output file. The step failed. Am I passing values wrongly ?
select count(*), avg(datalength(embedded_data)) from task_attach where
id_task_user in (select id from task_user where task_type=1 and status=7
and (date_begin >= CONVERT(DATETIME, str(datepart(YEAR,getdate()-
180),4) + '-' + str(datepart(MONTH,getdate()-180),2) + '-' +
str(datepart(DAY,getdate()-180),2) + ' 00:00:00', 102)));
GO
delete task_attach where id_task_user in (select id from task_user where
task_type=1 and status=7
and (date_begin >= CONVERT(DATETIME, str(datepart(YEAR,getdate()-
180),4) + '-' + str(datepart(MONTH,getdate()-180),2) + '-' +
str(datepart(DAY,getdate()-180),2) + ' 00:00:00', 102)));
GO
-- checkin
select count(*), avg(datalength(embedded_data)) from task_attach where
id_task_user in (select id from task_user where task_type=2
and (date_begin >= CONVERT(DATETIME, str(datepart(YEAR,getdate()-
180),4) + '-' + str(datepart(MONTH,getdate()-180),2) + '-' +
str(datepart(DAY,getdate()-180),2) + ' 00:00:00', 102)));
GO
delete task_attach where id_task_user in (select id from task_user where
task_type=2
and (date_begin >= CONVERT(DATETIME, str(datepart(YEAR,getdate()-
180),4) + '-' + str(datepart(MONTH,getdate()-180),2) + '-' +
str(datepart(DAY,getdate()-180),2) + ' 00:00:00', 102)));
GO
Regards,
NM
June 28, 2016 at 7:52 am
The only problem seems to be possible in the COUNT(*), try using COUNT_BIG(*) instead.
If you're actually deleting all those rows, be careful on filling the log.
Another possibility is that the problem is not in the code you shared, but somewhere else, probably a trigger.
Here are another ways to write your date calculation (a datetime column compared to a date value is still SARGable).
SELECT DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), -180)
,CONVERT(DATE,GETDATE()-180)
,CONVERT(DATETIME, str(datepart(YEAR,getdate()-180),4) + '-'
+ str(datepart(MONTH,getdate()-180),2) + '-'
+ str(datepart(DAY,getdate()-180),2) + ' 00:00:00', 102)
June 28, 2016 at 7:56 am
It says the problem with converting the expression to int.
I see only the date expression in this query, do you use any other expression also?
Also you can rewrite date_begin >= expression as datediff(day,date1,date2) > 0;
June 28, 2016 at 8:08 am
durga.palepu (6/28/2016)
@Luis,CONVERT(DATE,GETDATE()-180) may not work for the OP if he uses SQL Server 2008 or earlier.
Date data type is available since 2008.
June 28, 2016 at 8:18 am
Does the code execute successfully in SSMS?
select count(*), avg(datalength(embedded_data)) from task_attach where id_task_user in (
select id
from task_user
where task_type = 1 and [status] =7
and date_begin >= CAST(CAST(DATEADD(DAY,-180,GETDATE()) AS DATE) AS DATETIME)
);
GO
delete task_attach where id_task_user in (
select id
from task_user
where task_type = 1 and [status] = 7
and date_begin >= CAST(CAST(DATEADD(DAY,-180,GETDATE()) AS DATE) AS DATETIME)
);
GO
-- checkin
select count(*), avg(datalength(embedded_data)) from task_attach where id_task_user in (
select id
from task_user
where task_type = 2
and date_begin >= CAST(CAST(DATEADD(DAY,-180,GETDATE()) AS DATE) AS DATETIME)
);
GO
delete task_attach where id_task_user in (
select id
from task_user
where task_type = 2
and date_begin >= CAST(CAST(DATEADD(DAY,-180,GETDATE()) AS DATE) AS DATETIME)
);
GO
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 28, 2016 at 8:23 am
Exactly what I am thinking i.e. instead of using convert() its more accurate to use CAST() most of the times.
June 28, 2016 at 8:25 am
Yes the query gets executed successfully from SSMS apart from the third step where I get error Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
-- checkin
select count(*), avg(datalength(embedded_data)) from task_attach where id_task_user in (
select id
from task_user
where task_type = 2
and date_begin >= CAST(CAST(DATEADD(DAY,-180,GETDATE()) AS DATE) AS DATETIME)
);
GO
June 28, 2016 at 8:28 am
durga.palepu (6/28/2016)
Exactly what I am thinking i.e. instead of using convert() its more accurate to use CAST() most of the times.
No, it's not. CONVERT will allow to use format codes, CAST won't. Otherwise, they do exactly the same thing.
June 28, 2016 at 8:29 am
narayanamoorthy.a (6/28/2016)
Yes the query gets executed successfully from SSMS apart from the third step where I get error Msg 8115, Level 16, State 2, Line 1Arithmetic overflow error converting expression to data type int.
-- checkin
select count(*), avg(datalength(embedded_data)) from task_attach where id_task_user in (
select id
from task_user
where task_type = 2
and date_begin >= CAST(CAST(DATEADD(DAY,-180,GETDATE()) AS DATE) AS DATETIME)
);
GO
Have you tried with COUNT_BIG(*)?
June 28, 2016 at 8:32 am
Yes I used COUNT_BIG and got the same error. Also I don't use any other expression.
June 28, 2016 at 8:35 am
June 28, 2016 at 8:36 am
durga.palepu (6/28/2016)
It says the problem with converting the expression to int.I see only the date expression in this query, do you use any other expression also?
Also you can rewrite date_begin >= expression as datediff(day,date1,date2) > 0;
You can rewrite it that way, but you shouldn't in this particular case. The original version is SARGable, because the expression does not contain any fields. The "rewrite" is not SARGable, so it will not be able to use any relevant indexes.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 28, 2016 at 8:38 am
What is the datatype of the two columns in the join?
...id_task_user in (select id ...
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 28, 2016 at 8:40 am
Its foreign key. Unique identifier Not Null.
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply