Solved

How to calculate an SQL Agent Job Completion Time from msdb..sysjobhistory table in SQL 2005/2008

Posted on 2011-09-22
7
1,383 Views
Last Modified: 2012-06-27
Hello,
Does anyone know how to calculate a SQL Agent Job completion time from msdb..sysjobhistory table in SQL 2005/2008?
Basically, I have to combine values from three columns : [run_date], [run_time], [run_duration].

Thank you in advance
0
Comment
Question by:JOSHUABT
  • 4
  • 2
7 Comments
 
LVL 51

Expert Comment

by:HainKurt
ID: 36584656
they are int values, do you have any sample data for these 3 columns and a expected guess result :)
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36584661
0
 
LVL 51

Accepted Solution

by:
HainKurt earned 500 total points
ID: 36584666
this is the answer I guess
CONVERT(DATETIME, RTRIM(run_date)) + ((run_time/10000 * 3600) + ((run_time%10000)/100*60) + (run_time%10000)%100) / (86399.9964 /* Start Date Time */)

+ ((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100 /*run_duration_elapsed_seconds*/) / (86399.9964 /* seconds in a day*/) AS End_DateTime

Open in new window

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 5

Expert Comment

by:VENKAT KOKULLA
ID: 36585187

SELECT a.name AS job_name,
       a.enabled,
       a.description,
       a.date_created,
       a.date_modified,
       b.last_outcome_message,
       b.last_run_date,
       b.last_run_time,
       b.last_run_duration,
       convert(nvarchar(10),(b.last_run_duration/10000)) + ':' +
       convert(nvarchar(10),((b.last_run_duration%10000)/100)) + ':' +
       convert(nvarchar(10),(((b.last_run_duration%10000)%100)%100)) as duration,
       s.next_run_date,
       s.next_run_time,
       v.name,
       v.freq_type,
       v.freq_subday_type,
       v.freq_interval
  FROM MSDB.dbo.sysjobs AS a
  JOIN MSDB.dbo.sysjobservers AS b ON a.job_id = b.job_id
  JOIN MSDB.dbo.syscategories AS c ON a.category_id = c.category_id
  JOIN MSDB.dbo.sysjobschedules AS S ON A.job_id = s.job_id
  JOIN MSDB.dbo.sysschedules_localserver_view AS v ON s.schedule_id = v.schedule_id
 WHERE (c.name <> 'Report Server' )
 ORDER BY job_name


The above script will gives complete about the jobs running on the server with the job name, date modified,last outcoming message,last_run_date,last_run_time,duration,next_run_date,next_run_time etc;

Hope this will suffice your need.

--Venkat
0
 

Author Comment

by:JOSHUABT
ID: 36588515
Hainkurt,
Thank you for your response.
I think your T-SQL query answers my question.
I understand you convert run_duration to seconds and then divide it by total seconds of one day to covert it to day(s).
But one day has 86400 seconds and not 86399.9964.
Is there any reason why you divide it by 86399.9964 instead of 86400?

Thanks,
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36588685
to make exact precision you should use 86399.9964...
because one day is not actually 24*60*60 = 86400 exactly :) because of leap years etc...
if it does not matter +/- a few milliseconds, use 86400

0
 

Author Closing Comment

by:JOSHUABT
ID: 36588749
Thank you so much for your help.
I learned one day is actually shorter than I have thought to be for more than a half of centry :)


It is the question that enlightens us, not the answer.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now