msdb.dbo.sysjobs and last run date and time

The time is not stored as number of milliseconds. It is a numeric representation of the actual time. For instance, 12:06:59 is represented as 120659. 1:02:04 pm is presented as 130204. 1:23:45 am would be 12345.

Instead of calculating that all yourself, simply use the dbo.agent_datetime function.

Something like:

SELECT dbo.agent_datetime(last_run_date, last_run_time);

The above function uses this code to create a DATETIME value:

CONVERT(DATETIME,
      CONVERT(NVARCHAR(4),@date / 10000) + N'-' + 
      CONVERT(NVARCHAR(2),(@date % 10000)/100)  + N'-' +
      CONVERT(NVARCHAR(2),@date % 100) + N' ' +        
      CONVERT(NVARCHAR(2),@time / 10000) + N':' +        
      CONVERT(NVARCHAR(2),(@time % 10000)/100) + N':' +        
      CONVERT(NVARCHAR(2),@time % 100),
120)

If you can stand adding a function to the msdb, you can create a table-valued-function that will perform very well, as:

CREATE FUNCTION dbo.get_agent_datetime(@date INT, @time INT)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN (
SELECT agent_date_time = 
    CONVERT(DATETIME,
          CONVERT(NVARCHAR(4),@date / 10000) + N'-' + 
          CONVERT(NVARCHAR(2),(@date % 10000)/100)  + N'-' +
          CONVERT(NVARCHAR(2),@date % 100) + N' ' +        
          CONVERT(NVARCHAR(2),@time / 10000) + N':' +        
          CONVERT(NVARCHAR(2),(@time % 10000)/100) + N':' +        
          CONVERT(NVARCHAR(2),@time % 100),
    120)
);

To get the last 7 days of Agent Job History, you'd use this:

SELECT JobName = sj.name
    , RunDate = dt.agent_date_time
    , sjh.*
FROM dbo.sysjobs sj
    INNER JOIN dbo.sysjobhistory sjh ON sj.job_id = sjh.job_id
CROSS APPLY dbo.get_agent_datetime(sjh.run_date, sjh.run_time) dt
WHERE dt.agent_date_time >= DATEADD(DAY, -7, GETDATE());

Dates/times presented are stored in the local time of the server.


If you don't want to create a function in msdb or if you are not able to, you can use this:

select*,
dateadd(second, (last_run_time/10000*3600) + (((last_run_time%10000-last_run_time%100)/100)*60) + (last_run_time%100), convert(datetime,cast(nullif(last_run_date,0) as nvarchar(10)))) as [last_run_datetime] 
FROM msdb.dbo.sysjobservers

This can also be applied to [sysjobhistory] or other tables that use this strange integer representation of date and time

Or you can use function:

msdb.dbo.agent_datetime(last_run_date,last_run_time) as [last_run_datetime]

as pointed out by Max