March 31, 2015 at 1:11 pm
I have this script that I'm trying to filter the results of the Job History to the day prior at 1800 hours.
It return dates prior to what I have in the WHERE clause.
What should the WHERE Clause look like?
USE msdb
Go
SELECT j.name JobName,h.step_name StepName,
CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate,
STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime,
h.run_duration StepDuration,
case h.run_status when 0 then 'failed'
when 1 then 'Succeded'
when 2 then 'Retry'
when 3 then 'Cancelled'
when 4 then 'In Progress'
end as ExecutionStatus,
h.message MessageGenerated
FROM sysjobhistory h inner join sysjobs j
ON j.job_id = h.job_id
WHERE CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) >= '2015-03-30 18:0:00.000' -- This returns dates prior to 3/30/2015
ORDER BY j.name, h.run_date, h.run_time
GO
I also need the start and stop time.
Ideally it would reflect the information for each step.
Any help would be greatly appreciated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 31, 2015 at 3:42 pm
WHERE (CONVERT(SmallDATETIME, RTRIM(h.run_date)) +
(h.run_time * 9 + h.run_time % 10000 * 6 + h.run_time % 100 * 10) / 216e4 )>= cast('2015-03-30 18:0:00.000' AS datetime)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply