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