Function - msdb.dbo.agent_datetime

How did I miss this function?

For those that ever went through the pain of interpretting the run_date and run_time columns stored in msdb's sysjobhistory table, this function is a godsend. It returns a DATETIME format, that you can simply add the job duration to.

It appeared back in SQL 2005, and is demoed by this query ...

SELECT 
 run_date
,run_time
,msdb.dbo.agent_datetime(run_date,run_time) 
FROM msdb.dbo.sysjobhistory
ORDER BY run_date ,run_time

Various methods exist for doing the same in SQL 2000, all involving CAST/CONVERT to manipulate the strings and adding leading zeros where they are missing.
Here is my effort ...

SELECT 
 run_date
,run_time
,STUFF(STUFF(STR(run_date, 8, 0),5,0,'-'),8,0,'-') AS run_date_dateformat
,STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR,run_time),6), 3, 0, ':'), 6, 0, ':') AS run_date_timeformat
,CAST(STUFF(STUFF(STR(run_date, 8, 0),5,0,'-'),8,0,'-') + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR,run_time),6), 3, 0, ':'), 6, 0, ':') AS DATETIME) AS run_datetime_complete
FROM     msdb.dbo.sysjobhistory
ORDER BY run_date ,run_time