Blog Post

SQL Server – Get values as DATETIME from sysjobhistory


This is a follow-up post on SQL Server – Check SQL Agent Job History using T-SQL.

SQL Server Agent stores SQL jobs history in sysjobhistory. It has two different columns for date and time, Run_Date and Run_Time. Since this is not available as DATETIME we cannot filter based on certain criteria such as jobs that run in last 24 hours.

There is a system function available in msdb database which takes these two columns as input converts output to a DATETIME data type, dbo.agent_datetime. You can use this function as below:

SELECT        TOP 5


       [StepName]    HIST.step_name,

              [RunDateTime] dbo.agent_datetime(HIST.run_date,HIST.run_time)

FROM          sysjobs JOB

INNER JOIN    sysjobhistory HIST ON HIST.job_id JOB.job_id

ORDERBY      HIST.run_dateHIST.run_time

Result Set:

JobName                  StepName                            RunDateTime

syspolicy_purge_history  Verify that automation is enabled.  2013-01-29 02:00:00.000

syspolicy_purge_history  Purge history.                      2013-01-29 02:00:00.000

syspolicy_purge_history  Erase Phantom System Health Records 2013-01-29 02:00:00.000

syspolicy_purge_history  (Job outcome)                       2013-01-29 02:00:00.000

syspolicy_purge_history  Verify that automation is enabled.  2013-01-30 02:00:00.000


(5 row(s) affected)

Hope This Helps!


If you like this post, do like my Facebook Page -> SqlAndMe

EMail me your questions ->

Follow me on Twitter -> @SqlAndMe

Filed under: Catalog Views, SQL Agent, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012, Undocumented Functions


You rated this post out of 5. Change rating




You rated this post out of 5. Change rating