DBO.SysJobHistory, how do you get the Job Time?

  • In the MSDB MS is storing the information about a SQL Job, and in the DBO.SysJobHistory it has a column called "run_time".. Using SQL how do you convert this field to get the time the job ran?

  • dwilliscp (5/1/2012)


    In the MSDB MS is storing the information about a SQL Job, and in the DBO.SysJobHistory it has a column called "run_time".. Using SQL how do you convert this field to get the time the job ran?

    Are you asking at what time the job ran or how long the job (or job step) ran?

  • If what time, the integer value represents the time the job or job step started in the format hhmmss.

  • Hi Lynn,

    I can't remember where I got hold of this script but it does the conversion as you've asked:

    SELECT J.[name] JobName

    , J.[description] JobDescription

    , CONVERT(DATETIME, Cast(S.next_run_date AS VARCHAR), 112) AS NextRunDate

    , Datename(dw, CONVERT(DATETIME, Cast(S.next_run_date AS VARCHAR), 112)) AS NextRunDate_Day

    , S.next_run_time AS NextRunTime

    , (SELECT Min(run_time)

    FROM msdb.dbo.sysjobhistory H

    WHERE H.job_id = J.job_id) AS Duration_MIN

    , (SELECT Avg(run_time)

    FROM msdb.dbo.sysjobhistory H

    WHERE H.job_id = J.job_id) AS Duration_AVG

    , (SELECT Max(run_time)

    FROM msdb.dbo.sysjobhistory H

    WHERE H.job_id = J.job_id) AS Duration_MAX

    FROM msdb.dbo.sysjobs J

    LEFT JOIN msdb.dbo.sysjobschedules S

    ON J.job_id = S.job_id

    WHERE J.[Enabled] = 1

    :exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

  • Robin Sasson (5/1/2012)


    Hi Lynn,

    I can't remember where I got hold of this script but it does the conversion as you've asked:

    SELECT J.[name] JobName

    , J.[description] JobDescription

    , CONVERT(DATETIME, Cast(S.next_run_date AS VARCHAR), 112) AS NextRunDate

    , Datename(dw, CONVERT(DATETIME, Cast(S.next_run_date AS VARCHAR), 112)) AS NextRunDate_Day

    , S.next_run_time AS NextRunTime

    , (SELECT Min(run_time)

    FROM msdb.dbo.sysjobhistory H

    WHERE H.job_id = J.job_id) AS Duration_MIN

    , (SELECT Avg(run_time)

    FROM msdb.dbo.sysjobhistory H

    WHERE H.job_id = J.job_id) AS Duration_AVG

    , (SELECT Max(run_time)

    FROM msdb.dbo.sysjobhistory H

    WHERE H.job_id = J.job_id) AS Duration_MAX

    FROM msdb.dbo.sysjobs J

    LEFT JOIN msdb.dbo.sysjobschedules S

    ON J.job_id = S.job_id

    WHERE J.[Enabled] = 1

    ???

  • SELECT RUN_TIME

    FROM MSDB.DBO.SYSJOBHISTORY

    I have run_times from 200 up to 230200??? The run_date is YYYYMMDD but how you get the time the job ran from 200 or 230200 I have no idea.

  • Trying to answer the same question this morning found this page http://reportingservicestnt.blogspot.co.uk/2012/05/sql-server-failed-jobs-in-ssms.html

    Extracted what l wanted from the code, and this query l think answers the question

    SELECT JH.run_date ,

    JH.run_time ,

    CAST(CAST(JH.run_date AS VARCHAR) + ' '

    + LEFT(RIGHT('000000' + CAST(JH.run_time AS VARCHAR), 6), 2) + ':'

    + SUBSTRING(RIGHT('000000' + CAST(JH.run_time AS VARCHAR), 6), 3, 2)

    + ':' + RIGHT(RIGHT('000000' + CAST(JH.run_time AS VARCHAR), 6), 2) AS SMALLDATETIME) AS [DateTime] ,

    CAST(LEFT(RIGHT('000000' + CAST(JH.run_time AS VARCHAR), 6), 2) + ':'

    + SUBSTRING(RIGHT('000000' + CAST(JH.run_time AS VARCHAR), 6), 3, 2)

    + ':' + RIGHT(RIGHT('000000' + CAST(JH.run_time AS VARCHAR), 6), 2) AS TIME) AS [Time]

    FROM msdb.dbo.sysjobhistory AS JH

    A little late with a reply maybe help someone else.

    Or could also use the undocumented function here http://www.mssqltips.com/sqlservertip/2850/querying-sql-server-agent-job-history-data/

  • Instead of all the conversions you could use the scalar function msdb.dbo.agent_datetime.

    For example:

    declare @rundate int, @runtime int;

    set @rundate = 20140502;

    set @runtime = 512;

    select msdb.dbo.agent_datetime(@rundate,@runtime);

  • Hey thanks for the help.

  • Lynn Pettis (5/2/2014)


    Instead of all the conversions you could use the scalar function msdb.dbo.agent_datetime.

    For example:

    declare @rundate int, @runtime int;

    set @rundate = 20140502;

    set @runtime = 512;

    select msdb.dbo.agent_datetime(@rundate,@runtime);

    While that's convenient, if you ever need performance out of it, I don't believe you'll get it. Here's the code from that function. It's hard for me to believe they do conversions to NCHAR in this numeric-only process...

    CREATE FUNCTION agent_datetime(@date int, @time int)

    RETURNS DATETIME

    AS

    BEGIN

    RETURN

    (

    CONVERT(DATETIME,

    CONVERT(NVARCHAR(4),@date / 10000) + N'-' +

    CONVERT(NVARCHAR(2),(@date % 10000)/100) + N'-' +

    CONVERT(NVARCHAR(2),@date % 100) + N' ' +

    CONVERT(NVARCHAR(2),@time / 10000) + N':' +

    CONVERT(NVARCHAR(2),(@time % 10000)/100) + N':' +

    CONVERT(NVARCHAR(2),@time % 100),

    120)

    )

    END

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (5/18/2014)


    Lynn Pettis (5/2/2014)


    Instead of all the conversions you could use the scalar function msdb.dbo.agent_datetime.

    For example:

    declare @rundate int, @runtime int;

    set @rundate = 20140502;

    set @runtime = 512;

    select msdb.dbo.agent_datetime(@rundate,@runtime);

    While that's convenient, if you ever need performance out of it, I don't believe you'll get it. Here's the code from that function. It's hard for me to believe they do conversions to NCHAR in this numeric-only process...

    CREATE FUNCTION agent_datetime(@date int, @time int)

    RETURNS DATETIME

    AS

    BEGIN

    RETURN

    (

    CONVERT(DATETIME,

    CONVERT(NVARCHAR(4),@date / 10000) + N'-' +

    CONVERT(NVARCHAR(2),(@date % 10000)/100) + N'-' +

    CONVERT(NVARCHAR(2),@date % 100) + N' ' +

    CONVERT(NVARCHAR(2),@time / 10000) + N':' +

    CONVERT(NVARCHAR(2),(@time % 10000)/100) + N':' +

    CONVERT(NVARCHAR(2),@time % 100),

    120)

    )

    END

    True, it is inefficient. Here is another option:

    cast(cast(run_date as varchar(8)) + ' ' + stuff(stuff(right('000000' + cast(run_time as varchar(6)),6),5,0,':'),3,0,':') as datetime)

Viewing 11 posts - 1 through 10 (of 10 total)

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