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