select job_name, MAX(run_datetime) AS run_datetime, MAX(run_duration) AS run_duration

from
(
    select job_name, run_datetime,
        SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
        SUBSTRING(run_duration, 5, 2) AS run_duration
    from
    (
        select DISTINCT
            j.name as job_name, 
            run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +  
                (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
            run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
        from msdb..sysjobhistory h
        inner join msdb..sysjobs j
        on h.job_id = j.job_id
        WHERE j.name NOT LIKE 'Backup%'
AND j.name NOT LIKE 'Cleanup%'
AND j.name NOT LIKE 'Shrink%'
AND j.name <> 'WMI_Disk_Space_Notification' 
AND j.name <> 'syspolicy_purge_history'
    ) t
) t
WHERE run_dateTime >= '2013-03-09' AND run_dateTime < '2013-03-10'
GROUP BY job_name
order by job_name, run_datetime

/****** Object:  Table [dbo].[wrksysjobhistory]    Script Date: 3/10/2013 1:19:43 PM ******/
if object_id('dbo.wrksysjobhistory') is not null
    DROP TABLE [dbo].[wrksysjobhistory]
GO

/****** Object:  Table [dbo].[wrksysjobhistory]    Script Date: 3/10/2013 1:19:43 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[wrksysjobhistory](
	[instance_id] [int] IDENTITY(1,1) NOT NULL,
	[job_id] [uniqueidentifier] NOT NULL,
	[step_id] [int] NOT NULL,
	[step_name] [sysname] NOT NULL,
	[sql_message_id] [int] NOT NULL,
	[sql_severity] [int] NOT NULL,
	[message] [nvarchar](4000) NULL,
	[run_status] [int] NOT NULL,
	[run_date] [int] NOT NULL,
	[run_time] [int] NOT NULL,
	[run_duration] [int] NOT NULL,
	[operator_id_emailed] [int] NOT NULL,
	[operator_id_netsent] [int] NOT NULL,
	[operator_id_paged] [int] NOT NULL,
	[retries_attempted] [int] NOT NULL,
	[server] [sysname] NOT NULL
)

GO

/****** Object:  Table [dbo].[wrksysjobs]    Script Date: 3/10/2013 1:19:57 PM ******/
if object_id('dbo.wrksysjobs') is not null
    DROP TABLE [dbo].[wrksysjobs]
GO

/****** Object:  Table [dbo].[wrksysjobs]    Script Date: 3/10/2013 1:19:57 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[wrksysjobs](
	[job_id] [uniqueidentifier] NOT NULL,
	[originating_server_id] [int] NOT NULL,
	[name] [sysname] NOT NULL,
	[enabled] [tinyint] NOT NULL,
	[description] [nvarchar](512) NULL,
	[start_step_id] [int] NOT NULL,
	[category_id] [int] NOT NULL,
	[owner_sid] [varbinary](85) NOT NULL,
	[notify_level_eventlog] [int] NOT NULL,
	[notify_level_email] [int] NOT NULL,
	[notify_level_netsend] [int] NOT NULL,
	[notify_level_page] [int] NOT NULL,
	[notify_email_operator_id] [int] NOT NULL,
	[notify_netsend_operator_id] [int] NOT NULL,
	[notify_page_operator_id] [int] NOT NULL,
	[delete_level] [int] NOT NULL,
	[date_created] [datetime] NOT NULL,
	[date_modified] [datetime] NOT NULL,
	[version_number] [int] NOT NULL
) 

GO

SET ANSI_PADDING OFF
GO

select job_name, MAX(run_datetime) AS run_datetime, MAX(run_duration) AS run_duration,t.stop_execution_date

from
(
    select job_name, run_datetime,t.stop_execution_date,
        SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
        SUBSTRING(run_duration, 5, 2) AS run_duration
    from
    (
        select DISTINCT
            j.name as job_name,ja.stop_execution_date,ja.start_execution_date AS run_datetime,
            --run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +  
            --    (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
            run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
        from msdb.dbo.sysjobhistory h
        inner join msdb..sysjobs j
        on h.job_id = j.job_id
        CROSS APPLY (SELECT MAX(stop_execution_date) AS stop_execution_date,MAX(start_execution_date) AS Start_execution_date
						FROM msdb.dbo.sysjobactivity ja
						WHERE ja.job_id = j.job_id) ja
        WHERE j.name NOT LIKE 'Backup%'
AND j.name NOT LIKE 'Cleanup%'
AND j.name NOT LIKE 'Shrink%'
AND j.name <> 'WMI_Disk_Space_Notification' 
AND j.name <> 'syspolicy_purge_history'
    ) t
) t
WHERE run_dateTime >= '2013-03-08' AND run_dateTime <= '2013-03-11'
GROUP BY job_name,t.stop_execution_date
order by stop_execution_date DESC,job_name, run_datetime

SELECT DISTINCT j.name as job_name, 
	msdb.dbo.agent_datetime(run_date, run_time) AS [run_datetime],
	DATEADD(second,DATEDIFF(second,0,msdb.dbo.agent_datetime(19000101, run_duration)),msdb.dbo.agent_datetime(run_date, run_time)) AS [run_endtime]
FROM msdb..sysjobhistory h
INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id
WHERE j.name NOT LIKE 'Backup%'
AND j.name NOT LIKE 'Cleanup%'
AND j.name NOT LIKE 'Shrink%'
AND j.name <> 'WMI_Disk_Space_Notification' 
AND j.name <> 'syspolicy_purge_history'
ORDER BY run_endtime DESC

Far away is close at hand in the images of elsewhere.

Anon.

Far away is close at hand in the images of elsewhere.

Anon.