August 1, 2005 at 11:34 am
I'm looking for recomendations for the best way to convert SQL Job Durations from int (HHMMSS) to datetime values.
August 1, 2005 at 2:51 pm
When it comes right down to it, you can't convert SQL job durations to datetime values, because the duration is a measure of passed time--an interval--and a datetime is a precise moment or instant in time.
What you can do is convert that darn awkward msdb.dbo.sysJobHistory.run_duration format into something you can actually use. Here's my "starting template":
SELECT top 10
run_duration
,run_duration/10000 Hours
,run_duration/100%100 Minutes
,run_duration%100 Seconds
from sysJobHistory
order by run_duration desc
(This will list the longest durations you've got, and show that the resulting parsed-out values are accurate).
Based on this, you can produce a single value of the desired interval (hours, mintues, seconds, whatever), and apply that to the job at hand. A last example: this query takes a job id (xxx) and determines by job step how many entries there are, and--in rounded minutes--what the shorts, average, and longest runs were, along with the standard deviation (which is useful for telling how relevant the average is).
SELECT
step_id
,count(*) howMany
,min((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) lowest_Min
,avg((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) average_Min
,max((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) highest_Min
,stdev((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) stdev_Min
from sysJobHistory
where job_id = 'xxx'
and run_status = 1
group by
step_id
order by
step_id
The other obvious ploy is to calculate the single-value interval and then use the dateadd function, but how you set that up depends entirely upon what you're trying to accomplish.
Philip
May 2, 2008 at 8:41 am
This was great! If you're doing this you might also might need this: http://cookingwithsql.com/index.php?option=com_content&task=view&id=68&Itemid=9
[font="Comic Sans MS"]Tom Powell
http://philergia.wordpress.com/[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply