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?

9 Years
Discussion Span
Last Post by bajanpoet

CONVERT (varchar, GETDATE() - 1, 112) will give you yesterdays date in that format

Thanks, but I wanted it as an INTEGER, not a varchar... the script references the sysjobhistory table in the msdb database, and, in that table, the run_date column is an integer.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.