How do I get the correct date/time from the msdb.sysjob* tables?

You don't always have access to Enterprise Manager, so it would be nice to be able to figure out through other means when a job started and finished. The start time and execution time of SQL Server Agent jobs are stored in a system table in MSDB called sysjobhistory. Unfortunately, date and time are stored separately and, oddly enough, as integers... and the actual end time is not stored; you have to calculate it yourself. So, here is a script that will calculate the end times: 
 

SELECT      j.name,      endTime = DATEADD      (          SECOND,          jh.run_duration,          CAST          (              CONVERT(VARCHAR, jh.run_date)              + ' ' + STUFF(STUFF(RIGHT('000000'              + CONVERT(VARCHAR,jh.run_time),6),5,0,':'),3,0,':')              AS DATETIME          )      )  FROM      msdb..sysjobhistory jh  INNER JOIN      msdb..sysjobs j  ON      j.job_id = jh.job_id  WHERE      jh.step_name = '(Job outcome)'

 
Not exactly intuitive, but it works. Of course Steve Kass, who is way too smart for his own good, has a much tidier solution - at least in amount of code: 
 

SELECT      j.name,      endTime = CONVERT      (          DATETIME,          RTRIM(run_date)      )      +       (          run_time * 9          + run_time % 10000 * 6          + run_time % 100 * 10          + 25 * duration      ) / 216e4  FROM      msdb..sysjobhistory jh  INNER JOIN      msdb..sysjobs j  ON      j.job_id = jh.job_id  WHERE      jh.step_name = '(Job outcome)'

 
Remember that only so many entries are kept around in the job history table, so if you want a longer running history, you'll want to schedule a job (how ironic) to store this data in a peripheral table. 
 

---

Other options for formatting the date 
 
Here is some logic I whipped up in a request for dates in the format weekdayname, monthname mm, yyyy / HH:MM:SS: 
 

SELECT      j.name as JobName,      NextRunDate =      DATENAME(dw, CONVERT(CHAR(8), next_run_date, 112))      + ', '      + DATENAME(m, CONVERT(CHAR(8), next_run_date, 112))      + ' '      + CONVERT(VARCHAR(12), DAY(CONVERT(CHAR(8), next_run_date, 112)))      + ', '      + CONVERT(CHAR(4), YEAR(CONVERT(CHAR(8), next_run_date, 112))),      + STUFF(STUFF(RIGHT('000000'      + CONVERT(VARCHAR(8), next_run_time), 6), 5, 0, ':'), 3, 0, ':')  FROM      sysjobs j,      sysjobschedules js  WHERE      j.job_id = js.job_id

 
The code is much easier if you only want YYYYMMDD format for the date: 
 

SELECT      j.name as JobName,      NextRunDate =      CONVERT(CHAR(8), next_run_date, 112)      + ' '      + STUFF(STUFF(RIGHT('000000'      + CONVERT(VARCHAR(8), next_run_time), 6), 5, 0, ':'), 3, 0, ':')  FROM      sysjobs j,      sysjobschedules js  WHERE      j.job_id = js.job_id

 
Gert has created some great functions on his site for dealing with dates and times stored by SQL Server Agent.