?
Solved

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

Posted on 2011-09-22
7
Medium Priority
?
1,736 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 61

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 61

Expert Comment

by:HainKurt
ID: 36584661
0
 
LVL 61

Accepted Solution

by:
HainKurt earned 2000 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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 61

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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

593 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