Get the last 24 hour job record form msdb.dbo.sysjobhistory

Question

I want write a query to get the last 24 hours worth of job record from the "msdb.dbo.sysjobhistory" table, but I can't get because I get the "run_date" and "run_time" columns are returned as a number. How can I convert the "run_date" and "run_time" columns into a datetime variable, and use this to get the last 24 hour job history?

Polad

can you post some examples of the numbers you have? Or do you know the format? They might be epoch seconds, milliseconds, or formatted 20101008123456, or something else - which it is will affect the answer. – Mihe

i get the run_date in format like 20100628 and run_time like 192649 – Polad

Answer 1

Check out this post - it shows how to "decode" those run_date columns from sysjobhistory.

You should be able to get the entries from the last 24 hours with a query something like this:

SELECT 
    j.name as JobName, 
    LastRunDateTime = 
    CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' 
    + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121)
FROM 
    msdb..sysjobs j
INNER JOIN
    msdb..sysjobhistory jh ON j.job_id = jh.job_id
WHERE
    CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' 
    + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 121) > DATEADD(HOUR, -24, GETDATE())

Sneck

Hi,thanks for replys.actually i m executing the folloing query:: select sysjobs.job_id,sysjobs.name,sysjobhistory.step_name,sysjobhistory.message,sysjobhistory.run_status,sysjobhistory.run_date,sysjobhistory.run_time,sysjobhistory.run_duration,sysjobs.date_created,sysjobs.date_modified,sysjobs.version_number from sysjobhistory Inner join sysjobs on sysjobhistory.job_id=sysjobs.job_id how i can get record according to datetime filter – Polad

Answer 2

For databases after 2000, there is a function in the msdb database you can call that will return datetime:

msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime'

If you are using sql 2000, you can copy the source of that function from a later version and create it in your instance of 2000. I wish I could take credit for all of this, but I originally found it here: mssqltips.com

Wes

Answer 3

A co-worker of mine pointed out that the other given solutions only find jobs that we're started 24 hours ago (or less), not jobs that were completed 24 hours ago (or less). He proposed something along the lines of the following to location completed jobs:

SELECT j.name AS JobName
    ,LastCompletedDateTime = DATEADD(day, (run_duration / 240000), CONVERT(DATETIME, msdb.dbo.agent_datetime(run_date, run_time))) + 
        STUFF(STUFF(REPLACE(STR((run_duration % 240000), 7, 0), ' ', '0'), 4, 0, ':'), 7, 0, ':')
FROM msdb..sysjobs j
INNER JOIN msdb..sysjobhistory jh ON j.job_id = jh.job_id
WHERE DATEADD(day, (run_duration / 240000), CONVERT(DATETIME, msdb.dbo.agent_datetime(run_date, run_time))) + 
    STUFF(STUFF(REPLACE(STR((run_duration % 240000), 7, 0), ' ', '0'), 4, 0, ':'), 7, 0, ':') > DATEADD(HOUR, - 24, GETDATE())

I believe part of this comes from a blog, but we cannot locate it, when I do I'll link that as well...

Dumah