July 26, 2012 at 7:37 pm
The following script returns the run_duration as for example:
45028
43520
3007
40529
2509
I want 45028 to be formatted as 4:50:28 hh:mm:ss.
USE MSDB
GO
SELECT j.name AS JobName,h.step_name AS StepName,
CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) AS RunDate,
STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') AS RunTime,
h.run_duration AS 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 AS h
INNER JOIN sysjobs AS j
ON j.job_id = h.job_id
WHERE j.Name = 'AS400_Download'
AND h.step_name = '(Job outcome)'
AND h.run_duration > '1500'
ORDER BY j.name, h.run_date DESC , h.run_duration DESC--h.run_time DESC
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/
July 27, 2012 at 3:34 am
You do know that you had everything there you needed to do this, right?
here is my code, with your WHERE clause commented:
SELECT
j.name AS JobName,
h.step_name AS StepName,
CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS datetime), 111) AS RunDate,
STUFF(STUFF(RIGHT('000000' + CAST (h.run_time AS VARCHAR(6)),6),5,0,':'),3,0,':') AS RunTime,
STUFF(STUFF(RIGHT('000000' + CAST (h.run_duration AS VARCHAR(6)),6),5,0,':'),3,0,':') AS StepDuration,
--h.run_duration AS 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 AS h
INNER JOIN sysjobs AS j
ON j.job_id = h.job_id
--WHERE
-- j.Name = 'AS400_Download'
-- AND h.step_name = '(Job outcome)'
-- AND h.run_duration > '1500'
ORDER BY
j.name,
h.run_date DESC,
h.run_duration DESC--h.run_time DESC
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply