I confess this bit me hard in the #$% a while ago, twice: once when I found out that date and time were stored as an INT representation, once again when code failed b/c I forgot there could be a zero value for time with only 1 digit.

I won't present the following code I developed as a great solution, but b/c it's admin. code that runs but once a day as part of maintenance, I don't really care about performance. It works, and that was sufficient:

SELECT

j.name AS JobName,--NVARCHAR(128)

jh.step_id,--INT

jh.step_name,--NVARCHAR(128)

jh.sql_message_id,--INT

jh.sql_severity,--INT

--sysjobhistory natively stores run_date and run_time as separate integers. Combine and convert to DATETIME. Why MS, why??

CAST

(

--Date portion, which will always be an 8-digit INT in the form yyyymmdd:

CAST(jh.run_date AS VARCHAR(8)) + ' ' +

--Time portion is harder, b/c it can be 0, nnnnn (5 digits), or nnnnnn (6 digits) in the form hmmss. No leading zero.

--This construct will prepend 6 zeroes, then take the rightmost 6 characters, yielding a 6-character string:

--RIGHT('000000' + CAST(run_time AS VARCHAR(6)), 6)

--We then slice and re-format to hh:mm:ss and combine with the date, then cast the whole shebang as DATETIME.

LEFT(RIGHT('000000' + CAST(jh.run_time AS VARCHAR(6)), 6), 2) + ':' +

Substring(RIGHT('000000' + CAST(jh.run_time AS VARCHAR(6)), 6), 3, 2) + ':' +

RIGHT(RIGHT('000000' + CAST(jh.run_time AS VARCHAR(6)), 6), 2)

AS DATETIME) As RunDateTime,

jh.message,--NVARCHAR(1024)

jh.run_status,--INT

jh.run_duration--INT

INTO #t

FROM MSDB.dbo.sysjobs j INNER JOIN

MSDB.dbo.sysjobhistory jh ON j.job_id = jh.job_id

WHERE jh.sql_severity > 0 OR

jh.run_status = 0

Rich