quote:

---

Originally posted by TG

That info is already stored in a table: msdb..sysjobhistory. The [run_date] and [run_duration] values are stored in a funky format though. You may need to get creative to make use of them.

Be One with the Optimizer
TG

---

select job_name, run_datetime, run_duration
from
(
	select job_name, DATEADD(hh, -7, run_datetime) as run_datetime,
		SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
		SUBSTRING(run_duration, 5, 2) AS run_duration
	from
	(
		select 
			j.name as job_name, 
			run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +  
				(run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
			run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
		from msdb..sysjobhistory h
		inner join msdb..sysjobs j
		on h.job_id = j.job_id
		where run_date = '20080930' or run_date = '20081001'
	) t
) t
where 
	run_datetime between '10/01/2008 00:00' and '10/01/2008 05:00' and
	job_name <> 'backup transaction logs'
order by run_datetime