This set of code below shows all SQL Server Agent jobs that have failed for the date 200.03.29. The run_date column is an integer, but I want to run the code in such a way that the resultset is automatically generated for the day before the current date.
The code that works is below:
select [name] as [Job Name],[server] as [Server], step_id, step_name, [message] as [Error],
run_date as [Date Started], run_time as [Time Started], run_duration as [Duration]
from sysjobs join sysjobhistory on sysjobs.job_id = sysjobhistory.job_id
where run_date = 20090329
and run_status = 0
How can I change this so that run_date will get the equivalent integer value representing the day before the current date?