Getting end time from sysjobhistory

  • Judy Scheinuk

    Ten Centuries

    Points: 1307

    I haven't posted very much and I apologize in advance in case I do this wrong - but I am going nuts. I am using a bit of sql I found several places on the internet to calculate the end time for jobs in sysjobhistory. It gives me the wrong answer. It seems obvious that if a job starts at 6:05 and runs for 14 minutes, the end time should be something like 6:19. This routine gives me a time of 6:28. (That's double the run time but results are not consistent.)

    Here is the code and a few rows of the result. I am wondering if something has changed since this sql was devised. Any ideas?

    select job_name = sj.name, run_date, run_time, run_duration, endTime = CONVERT

    (

    DATETIME,

    RTRIM(run_date)

    )

    +

    (

    run_time * 9

    + run_time % 10000 * 6

    + run_time % 100 * 10

    + 25 * run_duration

    ) / 216e4

    FROM msdb.dbo.sysjobs sj

    INNER JOIN msdb.dbo.sysjobhistory sjh

    ON sj.job_id = sjh.job_id

    WHERE sj.name like '%Backup%'

    ORDER by run_date DESC

    DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1' 20060310 180500 1424 3/10/2006 6:28:44.000 PM

    DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1' 20060310 180500 1424 3/10/2006 6:28:44.000 PM

    DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1' 20060309 180500 1401 3/9/2006 6:28:21.000 PM

    DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1' 20060309 180500 1402 3/9/2006 6:28:22.000 PM

    DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1' 20060308 180500 1402 3/8/2006 6:28:22.000 PM

    DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1' 20060308 180500 1402 3/8/2006 6:28:22.000 PM

    DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1' 20060307 180500 1405 3/7/2006 6:28:25.000 PM

    DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1' 20060307 180500 1405 3/7/2006 6:28:25.000 PM

    DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1' 20060306 180500 1358 3/6/2006 6:27:38.000 PM

    DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1' 20060306 180500 1358 3/6/2006 6:27:38

  • BitWise MnM

    SSC Eights!

    Points: 859

    Try this script:

    use msdb

    go

    select

    left(b.run_date,4)+'/'+right(left(b.run_date,6),2)+'/'+right(b.run_date,2) as run_date,

    a.name,

    b.step_id,

    b.step_name,

    convert(varchar,(convert(datetime,(cast((CASE

    WHEN len(b.run_time)=6 THEN LEFT(b.run_time,2)

    WHEN len(b.run_time)=5 THEN LEFT(b.run_time,1)

    ELSE 0

    END) AS VARCHAR(2))+':'+

    cast((CASE

    WHEN len(b.run_time)>=4 THEN LEFT(RIGHT(b.run_time,4),2)

    WHEN len(b.run_time)=3 THEN LEFT(b.run_time,1)

    ELSE 0

    END) AS VARCHAR(2))+':'+RIGHT(b.run_time,2)))),108) as start_time,

    Convert(varchar,

    (

    convert(datetime,(cast((CASE

    WHEN len(run_time)=6 THEN LEFT(run_time,2)

    WHEN len(run_time)=5 THEN LEFT(run_time,1)

    ELSE 0

    END

    ) AS VARCHAR(2))+':'+

    cast((CASE

    WHEN len(run_time)>=4 THEN LEFT(RIGHT(run_time,4),2)

    WHEN len(run_time)=3 THEN LEFT(run_time,1)

    ELSE 0

    END) AS VARCHAR(2))+':'+RIGHT(run_time,2)))

    +

    convert(datetime,(cast((CASE

    WHEN len(run_duration)=6 THEN LEFT(run_duration,2)

    WHEN len(run_duration)=5 THEN LEFT(run_duration,1)

    ELSE 0

    END

    ) AS VARCHAR(2))+':'+

    cast((CASE

    WHEN len(run_duration)>=4 THEN LEFT(RIGHT(run_duration,4),2)

    WHEN len(run_duration)=3 THEN LEFT(run_duration,1)

    ELSE 0

    END) AS VARCHAR(2))+':'+RIGHT(run_duration,2)))

    ),108) as end_time

    from sysjobs a JOIN sysjobhistory b ON a.job_id=b.job_id

    WHERE

    (

    (b.run_time<=100000 AND b.run_date=convert(varchar,getdate(),112))

    OR

    (b.run_time>100000 AND b.run_date=convert(varchar,(dateadd(dd,-1,getdate())),112))

    )

    AND

    b.step_name<>'(Job outcome)'

    order by end_time

     

  • Judy Scheinuk

    Ten Centuries

    Points: 1307

    This does give me the right answer which is a definite improvement! I will want to study it a bit so I can figure out what it's doing.

    But if anyone has any insight on why the other routine produces a wrong answer, I'd be interested to know. I produced a report for a client with wrong times on it and I can't help wondering why.

    But thank you for the answer - I will use this as a basis for my script. It will save me a lot of time.

  • BitWise MnM

    SSC Eights!

    Points: 859

    I am not sure what is the logic of the script that you posted . The one I posted has a lot of code, but the logic is simple:

    take two right most digits and let that be seconds,

    take one or two, depending on overall legth, following digits and let that be minutes

    take one or two, depending on overall length, leftmost digits and let that be hours.

    Now put that together into a datetime format and and things up as appropriate.

  • Jules Bui

    SSC Eights!

    Points: 827

    Judy,

    If you noticed how Mordechai used the convert and cast functions in his script to get the accurate calculations of time.  The sysjobhistory datatypes for their run_time and run_date fields are int, so when you try to do just basic math calculations on this data, it does not calculate accurately unless you convert it to datetime.

     

     

    Jules Bui
    IT Operations DBA
    Backup and Restore Administrator

  • TDuffy

    SSCarpal Tunnel

    Points: 4170

    Or try this function:

    set

    ANSI_NULLS ON

    set

    QUOTED_IDENTIFIER ON

    go

     

    /****** Object: User Defined Function dbo.fn_AgentCompletionDateTime Script Date: 8/11/2005 2:31:03 PM ******/

    Create

     function [dbo].[fn_AgentCompletionDateTime] (@agentdate int, @agenttime int, @agentduration int)

    returns

    datetime

    as

    begin

    declare @date datetime,

    @year

    int,

    @month

    int,

    @day

    int,

    @hour

    int,

    @min-2

    int,

    @sec

    int,

    @datestr

    nvarchar(40),

    @runduration

    int

    select @year = (@agentdate / 10000)

    select @month = (@agentdate - (@year * 10000)) / 100

    select @day = (@agentdate - (@year * 10000) - (@month * 100))

    select @hour = (@agenttime / 10000)

    select @min-2 = (@agenttime - (@hour * 10000)) / 100

    select @sec = (@agenttime - (@hour * 10000) - (@min-2 * 100))

    select

    @runduration = case when @agentduration <100 then @agentduration

    when @agentduration between 100 and 999 then left(@agentduration,1)*60+right(@agentduration,2)

    when @agentduration between 1000 and 9999 then left(@agentduration,2)*60+right(@agentduration,2)

    when @agentduration > 9999 then left(@agentduration,len(@agentduration)-4)*3600+left(right(@agentduration,4),2)*60+right(@agentduration,2)

    end

    select @datestr = convert(varchar(4), @year) + N'-' +

    convert(varchar(2), @month) + N'-' +

    convert(varchar(4), @day) + N' ' +

    replace(convert(varchar(2), @hour) + N':' +

    convert(varchar(2), @min-2) + N':' +

    convert(varchar(2), @sec), ' ', '0')

    select @date = convert(datetime, @datestr)

    select @date =dateadd(ss,@runduration,@date)

    return @date

    end

     

     

     

     

     

  • Judy Scheinuk

    Ten Centuries

    Points: 1307

    I did get the drift of Mordechai's solution but thanks for explaining it so simply. Also for the function which might be a useful way of handling this ultimately. My nephew who is graduating with a degree in math and his father who is also a mathemetician looked over the first script and they think the math is OK but it does seem likely that a pure math solution applied to a date/time problem might not be accurate enough. For the record - 216e4 translates to 216*10 to the 4th which translates to 60*60*60*10 - must have something to do with time.

  • BitWise MnM

    SSC Eights!

    Points: 859

    The original query you posted seems like an elegant solution. If you have the time, may be you could explain the logic behind this segment:

    (

    run_time * 9

    + run_time % 10000 * 6

    + run_time % 100 * 10

    + 25 * run_duration

    ) / 216e4

     

    Obviously it is the one giving inconsistent results, but not knowing what is the math behind it, I don't think I can troubleshoot it.

  • Thomas Pliska

    SSC Journeyman

    Points: 87

    Judy, the reason that the solution you have does not work is because the author assumed that the run_duration field was the number of seconds that the job ran.  They were incorrect.  the run_duration field is just like the run_time field in that it is in the format HHMMSS. basically, the author is adding the decimal fraction of a day that is represented in run_time to midnight of the day in run_date.  he/she calculates this fraction by considering the integer representation of hhmmss as a sexagisemal value.  there is a caveat in there that has to do with the fact that there is a difference in order of magnitude but I'm not going there in this post.  Bottom line 1 sec = 25/2160000ths of a day.  Thats why he/she was multiplying the duration by 25.  

    try this and see if it works...

    (

    run_time * 9

    + run_time % 10000 * 6

    + run_time % 100 * 10

    + run_duration * 9

    + run_duration % 10000 * 6

    + run_duration % 100 * 10

    ) / 216e4

  • Peter Graus

    SSC Journeyman

    Points: 82

    I don't know the way to get end time,

    but I think, the simpliest way to get starting datetime from sysjobhistory is:

    STR(run_date,8)+' '+STUFF(STUFF(RIGHT(1000000+run_time,6),5,0,':'),3,0,':')

    or

    CONVERT(DATETIME,STR(run_date,8)+' '+STUFF(STUFF(RIGHT(1000000+run_time,6),5,0,':'),3,0,':'))

    .

  • Peter Graus

    SSC Journeyman

    Points: 82

    When run_duration exceeded 100 hours than LEN(run_duration)>6..

    The end time could be counted like this:

    DATEADD(second,

    [run_duration]/10000*3600+[run_duration]%10000/100*60+[run_duration]%100,

    STR(run_date,8)+' '+STUFF(STUFF(RIGHT(1000000+run_time,6),5,0,':'),3,0,':'))

  • vijred

    SSChasing Mays

    Points: 621

     

    select top 10000 instance_id, job_name = sj.name, run_date, run_time, run_duration

    --,convert(DATETIME,RTRIM(run_date)) as myrundate

    --,convert(int,(run_time / 10000)) as run_time_hour

    --,convert(int,(run_time / 100))%100 as run_time_minutes

    --,convert(int,(run_time / 1))%100 as run_time_seconds

    --,convert(int,(run_duration / 1000000)) as run_duration_days

    --,convert(int,(run_duration / 10000))%100 as run_duration_hour

    --,convert(int,(run_duration / 100))%100 as run_duration_minutes

    --,convert(int,(run_duration / 1))%100 as run_duration_seconds

    , dateadd(second,  ( convert(int,(run_time / 10000)) * 3600 )

    + (convert(int,(run_time / 100))%100) * 60

    +  (convert(int,(run_time / 1))%100)

    + ( convert(int,(run_duration / 1000000))) * 3600 * 24

    +  (convert(int,(run_duration / 10000))%100) * 3600

    + (convert(int,(run_duration / 100))%100) * 60

    + (convert(int,(run_duration / 1))%100)

    ,convert(DATETIME,RTRIM(run_date))) as StepCompleteDateTime

    FROM msdb.dbo.sysjobs sj

    INNER JOIN msdb.dbo.sysjobhistory sjh

    ON sj.job_id = sjh.job_id

    -- -- https://vijredblog.wordpress.com/2020/01/27/sql-job-history-getting-end-time-from-sysjobhistory/

    -Vijred (http://vijredblog.wordpress.com)

  • fredclown

    Newbie

    Points: 1

    There is a built-in SQL function called msdb.dbo.agent_datetime that will convert the run_date and run_time into a datetime. From there it is simply a matter of doing a dateadd of the hours, minutes, and seconds to get the end time of the job. I think the below SQL is a bit more succinct.

    select
    J.name,
    dbo.agent_datetime(H.run_date, H.run_time) start_time,
    dateadd(second, run_duration%100, dateadd(minute, run_duration/100%100, dateadd(hour, run_duration/10000, dbo.agent_datetime(H.run_date, H.run_time)))) end_time,
    H.run_date,
    H.run_time,
    run_duration
    from msdb.dbo.sysjobs J
    inner join msdb.dbo.sysjobhistory H on J.job_id = H.job_id

Viewing 13 posts - 1 through 13 (of 13 total)

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