Get DATETIME Value From msdb.dbo.sysjobhistory

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