Question:  How can I programmatically return  SQL Agent jobs which failed in the last 24 hrs.   I was running the  following code and it didn’t return the results as expected ?

This job is part of some automated DBA tasks 

 SELECT * 
FROM msdb..sysjobhistory 
WHERE run_status NOT IN (1,4)
DATEDIFF(hh, CONVERT(datetime,CONVERT(VARCHAR(8),sjh.run_date),12), GETDATE()) <= 24

Answer: The code above will return failed jobs via the  WHERE run_status NOT IN (1,4)

, but not for the timeframe you expect. The reason: The code doesn’t account for sjh.run_date being an INT.     The CONVERT is not having the desired result.

Use the msdb.dbo.agent_datetime function. The msdb.dbo.agent_datetime function converts run_date &  run_time into datetime values.  This function  allows for much cleaner code , and makes you wonder why the datatype was never a datetime originally. 

This will return the desired result:

SELECT * 
FROM msdb..sysjobhistory 
WHERE run_status NOT IN (1,4)
DATEDIFF(hh, msdb.dbo.agent_datetime(run_date,run_time), GETDATE()) <= 24

For further automation read, Read List failed SQL server Jobs with Powershell