Get the last 24 hour job record form msdb.dbo.sysjobhistory
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())
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