If you ever had to work with SQL Job Agent and find out the details about run time for the job/step, you would find it difficult to convert the INT into DATETIME. Here is the quick solution.
- To get the start date time, use msdb.dbo.agent_datetime function available in SQL Server 2005 onwards. If you are using older version of SQL Server, you may search for details of this function and utilize the same script that is used in the function.
- To get the end date time, convert the run_duration into seconds and use DATEADD function along with start date time to get the end datetime.
Here is a full SQL script
-- Create table variable to hold history data DECLARE @xp_result TABLE ( job_id UNIQUEIDENTIFIER, step_id INT, run_date INT, run_time INT, run_duration INT, run_duration_seconds INT, run_start_time DATETIME, run_end_time DATETIME ) -- Get history data INSERT INTO @xp_result (job_id, step_id, run_date, run_time, run_duration) SELECT h.job_id, h.step_id, h.run_date, h.run_time, h.run_duration FROM msdb.dbo.sysjobhistory h -- Get run duration in seconds UPDATE @xp_result SET run_duration_seconds = ((run_duration / 10000)*3600) + (((run_duration / 100) % 100) * 60) +( (run_duration) % 100) -- Calculate start date time UPDATE @xp_result SET run_start_time = msdb.dbo.agent_datetime(run_date, run_time) -- Calculate end date time UPDATE @xp_result SET run_end_time = DATEADD(SS, run_duration_seconds, run_start_time) -- Show resultset SELECT * FROM @xp_result
Vishal Monpara is a full stack Solution Developer/Architect with 13 years of experience primarily using Microsoft stack. He is currently working in Retail industry and moving 1’s and 0’s from geographically dispersed hard disks to geographically dispersed user leveraging geographically dispersed team members.