# Calculating Job finish time

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,':') )
``````

### >Solution :

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.*,