Thanks Jeremy...nu muss no fuss...straight to point...cheers

Great tip and just what I was looking for, many thanks!

I made a couple of tweaks for ease or reading, would you proivde feedback please.

1st, the dates:

-- Variable Declarations
DECLARE @PreviousDate datetime
DECLARE @Year VARCHAR(4)
DECLARE @Month VARCHAR(2)
DECLARE @MonthPre VARCHAR(2)
DECLARE @Day VARCHAR(2)
DECLARE @DayPre VARCHAR(2)
DECLARE @FinalDate INT

-- Initialize Variables
SET @PreviousDate = DATEADD(dd, -7, GETDATE()) -- Last 7 days 
SET @Year = DATEPART(yyyy, @PreviousDate) 
SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))
SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2)
SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))
SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2)
SET @FinalDate = CAST(@Year + @Month + @Day AS INT)

print @finaldate

--test convert in one
print convert(varchar(30), DATEADD(dd, -7, GETDATE()), 112)

Both return the same and work within the code so I altered it and all is good... I think :)

SELECT h.server                                          AS [SQL Server],
       j.[name]                                          AS [Job Name],
       s.step_name                                       AS [Historic Step Name],
        h.step_id                                         AS [StepID],
       h.step_name                                       AS [Current Step Name],
        LEFT(h.run_date, 4) + '-'
       + Substring(Cast(h.run_date AS VARCHAR(8)), 5, 2)
       + '-' + RIGHT(h.run_date, 2)                      AS [Run Date],
       LEFT(h.run_time, 2) + ':'
       + Substring(Cast(h.run_time AS VARCHAR(8)), 3, 2) AS [Run Time],
       h.sql_severity                                    AS [Severity],
       h.message                                         AS [Message]

FROM   msdb.dbo.sysjobhistory h
       INNER JOIN msdb.dbo.sysjobs j
               ON h.job_id = j.job_id
       INNER JOIN msdb.dbo.sysjobsteps s
               ON j.job_id = s.job_id
                  AND h.step_id = s.step_id

WHERE  h.run_status = 0 -- Failure 
       AND h.run_date > CONVERT(VARCHAR(30), Dateadd(dd, -7, Getdate()), 112)

ORDER  BY h.instance_id DESC,
          h.run_date DESC,
          h.run_time DESC 

Sorry, just a further point that's interesting:

With the alteration I've suggested the query optimiser applies more weight to the final clusted index seek(sysjobs.clust [j] 17% as opposed to 11% with the original) and less wieght on the initial clustered index scan (sysjobhistory.clust [h] 77% opposed to 83% in the original). It also estimates the number of rows much more efficiently, actual is 36, estimaged is 36.642, very close. In the original with the variable, estimaged is 11.4.

Perhaps an agument against variable :)

Thanks again, been fun and I've got it stored to use daily!

Martyn,

Thank you for the feedback.  This looks good.  I hope to update this tip in the near term with some additional items.

Thank you,
Jeremy Kadlec