Post reply

sysjobs/jobhsitory meaningless run_time and run_Duration

  • March 15, 2005 at 1:50 pm

    #85638

    select name, sjh.step_id, sjh.step_name, sjh.run_duration, sjh.run_date, sjh.run_time

    from sysjobhistory sjh

    join sysjobs sj on sjh.job_id = sj.job_id

    where name like 'DoD%'

    order by run_date desc, name, sjh.step_id

    For the above SQl the duration and run time are meaning less. I'm looking for duration HH:MM:SS and run time HH:MM:SS.

    Is this possible?

     

    Thanks!

  • March 15, 2005 at 1:53 pm

    #547161

    You could probably find this information in the Windows event log, if you are logging informational messages.



    A.J.
    DBA with an attitude

  • March 15, 2005 at 2:00 pm

    #547163

    We don't log ... it is a giant job with steps that run DTS packages, TSQL, and stored procedures.  Any conversion sp_'s out there?

  • March 15, 2005 at 2:38 pm

    #547167

    I think the magical potion you're looking for is something like

        CONVERT(datetime, STUFF(STUFF(sjh.run_date,7,0,'-'),5,0,'-') 
        + ' ' + STUFF(STUFF(REPLACE(STR(sjh.run_time,6),' ','0'),5,0,':'),3,0,':'), 120)

    You might need to add "SET DATEFORMAT ymd" at the start so the date is interpreted correctly.

     

    --------------------
    Colt 45 - the original point and click interface

  • March 15, 2005 at 3:02 pm

    #547170

    Wonderful.  Worked perfrectly.  Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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