SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Job Duration conversion from int (HHMMSS) to datetime


SQL Job Duration conversion from int (HHMMSS) to datetime

Author
Message
Roger Falor
Roger Falor
SSC-Enthusiastic
SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)

Group: General Forum Members
Points: 140 Visits: 31
I'm looking for recomendations for the best way to convert SQL Job Durations from int (HHMMSS) to datetime values.
Philip Kelley
Philip Kelley
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2899 Visits: 232

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





Tom Powell-334692
Tom Powell-334692
SSC Veteran
SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)

Group: General Forum Members
Points: 208 Visits: 117
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

Tom Powell
http://philergia.wordpress.com/

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search