Convert sysjobhistory run_date and run_time into SQL DATETIME

[TJRTech]

SQLAgent stores job history execution in seperate run_date and run_time integer columns, in the format:

run_date = YYYYMMDD
run_time = HHMMSS

And, since these are integers and seperate columns they are a pain to force into a SQL DATETIME, but HERE is one way:

SELECT RUN_DATE_TIME = convert( datetime,
         convert(varchar, run_date/10000)+'/'+
         convert(varchar, run_date%1000/100)+'/'+
         convert(varchar, run_date%100)+' '+
         convert(varchar, run_time/10000)+':'+
         convert(varchar, run_time%10000/100)+':'+
         convert(varchar, run_time%100)+'.000' )
FROM msdb.dbo.sysjobhistory

Regards,
TR