Calculating Job finish time

Question

So msdb.dbo.sysjobhistory has start time and some integers out of which you can calculate an end time

SELECT CONVERT(datetime, msdb.dbo.agent_datetime(run_date, run_time))+
        CONVERT(datetime, STUFF(STUFF(RIGHT('000000' + CAST ( run_duration AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') ) As JobFinishTime
 FROM  msdb.dbo.sysjobs j  
 INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id  
 where h.step_id = 0 AND h.run_status=1 AND j.enabled = 1  
 AND  j.enabled = 1

so one of my jobs has run_duration value of 250910, which is 25 hours, 9 minutes, 10 seconds. Obviously conversion fails since it is more than 24 hours. Can you please help me fix above query when value of run_duration is 250910

Use this as reference, filled run_date and run_time with some values

select CONVERT(datetime, msdb.dbo.agent_datetime(20230411, 70358))+
        CONVERT(datetime, STUFF(STUFF(RIGHT('000000' + CAST ( 250910 AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') ) 

Answer 1

As I understand your question, you have a start date and a duration expressed as a string in 'HHMMSS' format, from which you want to derive an end date. One approach uses string functions to separate the hours, minutes and seconds, convert the whole thing to a number of seconds, and then offset the start date with dateadd().

Assuming that you have the start date in column jobStartTime and the duration in runDuration, you could express this like:

dateadd(
    second, 
    left(runDuration, 2) * 60 * 60 + substring(runDuration, 3, 2) * 60 + right(runDuration, 2),
    jobStartTime
) JobFinishTime

Here is a contrived example:

select x.*, 
    dateadd(
        second, 
        left(duration, 2) * 60 * 60 + substring(duration, 3, 2) * 60 + right(duration, 2),
        startdt
    ) enddt
from ( values (getdate(), '250910')) as x(startdt, duration)

startdt	duration	enddt
2023-05-02 13:34:12.203	250910	2023-05-03 14:43:22.203

fiddle