Calcute End_datetime using Start_datetime plus Run_duration in hour format

SQL Agent Jobs; In msdb you have dbo.sysjobhistory who gives you Run_date and Run_time as type INT which easily can convert to Start_Datetime with dbo.agent_datetime. I get: 2018-05-17 01:13:09.240. Run_duration is INT as well. I use STUFF(STUFF(RIGHT('000000' + CAST ( jh.run_duration AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') as 'Time_HH:MM:SS'. I get 27:31:20

I want to calculate and to have a new column as End_datetime. (i.e Start_datetime + Run_duration) My jobs are running more than 24h etc so date can go inte next day as well.

Where to start? After conversion or start with the INT colummns Run_date, Run_time, Run_duration?

Simpliest script would be most helpful.

DATEADD (hh, Run_duration , Start_datetime )

https://docs.microsoft.com/en-us/sql/t-sql/functions/dateadd-transact-sql?view=sql-server-2017

DATEADD(SECOND, run_duration / 10000 * 3600 + run_duration % 10000 / 100 * 60 + run_duration % 100, 
    msdb.dbo.agent_datetime(jh.run_date, jh.run_time)) AS end_datetime,
2 Likes

Thanks for a good short script!
May I ask one more? The Run_duration column below have as a first example of 122hours and then my conversion with Stuff as mentioned above gives 00:00:00* with the asterix when hours are above 100
May I ask for a Better conversion script?

|> run_duration|Time_HH:MM:SS|

|---|---|
|1220239|00:00:0*|
|1184837|00:00:0*|
|1180304|00:00:0*|
|1141222|00:00:0*|
|991243|99:12:43|

That's complex enough that you need to add a CROSS APPLY to the original query to calc the run_duration len but with a minimum len of 6. Like this:

SELECT TOP (20) run_duration,
    STUFF(STUFF(RIGHT(REPLICATE('0', run_duration_len) + CAST(jh.run_duration AS varchar(10)), run_duration_len),run_duration_len - 1,0,':'),run_duration_len - 3,0,':') AS 'Time_HH:MM:SS'
--FROM ( values(1000010),(1234567),(12345678) ) jh(run_duration)
FROM msdb.dbo.sysjobhistory jh
CROSS APPLY (
    SELECT CASE WHEN LEN(jh.run_duration) < 6 THEN 6 ELSE LEN(jh.run_duration) END AS run_duration_len
) AS alias1

By using these lines I did manage to get minutes and seconds when hours are than 99 ie 3 digits. I would be thankful for comments if there is some limitation in my solution (due to me beeing a beginner in SQL)

,((run_duration/100003600 + (run_duration/100)%10060 + run_duration%100 + 31 ) / 60) as RunDurationMinutes
, ((run_duration / 10000 * 3600 + run_duration % 10000 / 100 * 60 + run_duration % 100)) as RunDurationSeconds

Results where run_duration is more 99hours;
|run_duration |RunDurationMinutes |RunDurationSeconds|
|1220239 |7323 |439359|