June 26, 2008 at 11:07 am
I am looking for a query that takes the run_date and run_time column from sysjobhostory and concats them together into a datetime. I have researched this for weeks and cannot find a way to do it in one query without temp variables and temp tables. I basically want to write a query that gives me the job name, and the run datetime (calcualted value I am trying to find) so I can order by the datetime and query against that value
June 26, 2008 at 11:28 am
I found what I was looking for
select
j.name as 'JobName',
CAST ( LEFT(CAST(run_date as varchar(8)),4) + '/' + SUBSTRING(CAST(run_date as varchar(8)), 5,2) + '/' +
RIGHT(cast(run_date as varchar(8)), 2) + ' ' +
CAST( ((run_time/10000) %100) as varchar ) + ':' + CAST( ((run_time/100) %100) as varchar ) + ':' +
CAST( (run_time %100) as varchar )as datetime ) as 'RunDateTime',
((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) as 'RunDurationMinutes'
From sysjobs j LEFT OUTER JOIN sysjobhistory h ON j.job_id = h.job_id
where h.step_id = 0 and j.enabled = 1
order by JobName, RunDateTime desc
June 26, 2008 at 11:39 am
Or something like:
SELECT substring(cast(run_date as char),1,4)+'-'+substring(cast(run_date as char),5,2)+'-'+substring(cast(run_date as char),7,2)+ ' '
+ STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':')
FROM msdb.dbo.sysjobhistory
Maninder
www.dbanation.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply