SQL Job Duration conversion from int (HHMMSS) to datetime

  • Roger Falor

    Old Hand

    Points: 332

    I'm looking for recomendations for the best way to convert SQL Job Durations from int (HHMMSS) to datetime values.

  • Philip Kelley

    SSCertifiable

    Points: 6351

    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

    Old Hand

    Points: 366

    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 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply