msdb.dbo.sysjobs and last run date and time

I know that the last_run_date and last_run_time are stored as Int, but when coverted over to datetime (of which I have examples of), is the time GMT, or what? When converted it doesn't match the same time as what the GUI log history displays.

Can anyone provide guidance on this?

Here is the t-sql code I have that converts the value to a datetime value:

'last run literal' = dateadd(millisecond, 
    sjs.last_run_time,
    convert(datetime,cast(nullif(sjs.last_run_date,0) as nvarchar(10))))

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.

StackExchange Database Administrators Q#136894, answer score: 9