SQL Server - DateTime Conversion field Issue

Got following fields in table:

Run Date : 2011-09-25 00:00:00.000

Run Time : 05:00:00

Run Duration : 03:22:51

What I need is in Dateformat

Run Date + Run Time = Start Time of Job (DateTime Format)

Run Date + (Run Time + Run Duration) = End Time of Job (DateTime Format)

I'm struggling to do conversion. Can anyone please help.

This is the STORED PROCEDURE which I'm using - can anyone advise how to monitor this:

ALTER PROCEDURE [dbo].[Sp_listjobrunhistory] @dateparam DATETIME, @JobName VARCHAR(100) AS BEGIN SELECT --sysjobhistory.server, sysjobs.name AS job_name, CASE sysjobhistory.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' ELSE '???' END AS run_status, CAST( Isnull(Substring(CONVERT(VARCHAR(8), run_date), 1, 4) + '-' + Substring(CONVERT(VARCHAR (8), run_date), 5, 2) + '-' + Substring(CONVERT(VARCHAR( 8), run_date), 7, 2), '') AS DATETIME) AS [Run DATE],

         Isnull(Substring(CONVERT(VARCHAR(7), run_time+1000000), 2, 2) + ':'
                 +
                       Substring(CONVERT(VARCHAR(7), run_time+1000000), 4, 2
                        )
                +
                ':' +
                Substring(CONVERT(VARCHAR(7), run_time+1000000), 6, 2), '') 
         AS
         [Run TIME],
         Isnull(Substring(CONVERT(VARCHAR(7), run_duration+1000000), 2, 2) +
                 ':' +
                       Substring(CONVERT(VARCHAR(7), run_duration+1000000),
                       4,
                       2)
                + ':' +
                Substring(CONVERT(VARCHAR(7), run_duration+1000000), 6, 2),
         ''
         ) AS
         [Duration],
         Isnull(Substring(CONVERT(VARCHAR(7), run_time+run_duration+1000000), 2, 2) + ':'
                 +
                       Substring(CONVERT(VARCHAR(7), run_time+run_duration+1000000), 4, 2
                        )
                +
                ':' +
                Substring(CONVERT(VARCHAR(7), run_time+run_duration+1000000), 6, 2), '')
         AS
         [Total TIME],             
         sysjobhistory.step_id,
         sysjobhistory.ste开发者_StackOverflow社区p_name,
         sysjobhistory.MESSAGE
  FROM   msdb.dbo.sysjobhistory
         INNER JOIN msdb.dbo.sysjobs
           ON msdb.dbo.sysjobhistory.job_id = msdb.dbo.sysjobs.job_id
  WHERE  sysjobhistory.run_date <= Datepart(yyyy, @dateparam) * 10000 +
                                         Datepart(mm, @dateparam) * 100 +
                                  Datepart
                                  (
                                         dd, @dateparam)
         AND sysjobs.name = @JobName --remove this line if you want to show all jobs for the specified day
  ORDER  BY instance_id DESC

END Regards

---

In SQL Server 2008, no conversion is required.

declare @T table
(
  RunDate datetime,
  RunTime time,
  RunDuration time
)

insert into @T values('2011-09-25 00:00:00.000', '05:00:00', '03:22:51')

select RunDate + RunTime as StartTimeOfJob,
       RunDate + RunTime + RunDuration as EndTimeOfJob
from @T

And in versions before 2008 it could be like this.

declare @T table
(
  RunDate datetime,
  RunTime varchar(8),
  RunDuration varchar(8)
)

insert into @T values(
'2011-09-25 00:00:00.000',
'05:00:00',
'03:22:51')

select RunDate + RunTime as StartTimeOfJob,
       RunDate + RunTime + RunDuration as EndTimeOfJob
from @T

Ooops. No conversions needed there either.

---

Here is the code to accomplish what you are looking for:

create table dbo.RunDurationTest
(
    RunDate datetime not null,
    RunTime time not null,
    RunDuration time not null
)

insert into rundurationtest
values ('2011-09-25 00:00:00.000', '05:00:00', '03:22:51')

select 
    (RunDate + RunTime) as RunDateTime,
    (RunDate + RunTime + RunDuration) as EndRunDateTime,
    *
from rundurationtest

---

Assuming that the [Run Time] and [Run Duration] are varchar fields, I'd have the following:

select StartJob = [Run Date] + convert(datetime, [Run Time])

select EndJob   = StartJob + convert(datetime, [Run Duration])