SQL Server Calculating Job finish time

twh00eeo  于 2023-05-05  发布在  其他
关注(0)|答案(2)|浏览(179)

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

yi0zb3m41#

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)
startdtdurationenddt
2023-05-02 13:34:12.2032509102023-05-03 14:43:22.203

fiddle

wztqucjr

wztqucjr2#

I understand that you want to extract active job that have lasted more than 24 hours

SELECT sj.name
,  sja.start_execution_date,
DATEDIFF(minute,sja.start_execution_date,getdate())  as Runtime
FROM msdb.dbo.sysjobactivity AS sja
INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
WHERE sja.start_execution_date IS NOT NULL
AND sja.stop_execution_date IS NULL
and DATEDIFF(minute,sja.start_execution_date,getdate())>=24

History Job:

SELECT j.Name, jh.Step_name,

CONVERT(DATETIME, RTRIM(jh.run_date)) 
+ ((jh.run_time/10000 * 3600) 
+ ((jh.run_time%10000)/100*60) +
(jh.run_time%10000)%100 ) / (23.999999*3600 )
AS Start_DateTime
,jh.run_duration,

CONVERT(DATETIME, 
RTRIM(jh.run_date)) + ((jh.run_time/10000 * 3600) + ((jh.run_time%10000)/100*60) +
(jh.run_time%10000)%100) / (86399.9964)
+ ((jh.run_duration/10000 * 3600) + ((jh.run_duration%10000)/100*60) + (jh.run_duration%10000)%100
) / (86399.9964) AS End_DateTime

from msdb..sysjobhistory jh, msdb..sysjobs j
where jh.job_id=j.job_id
--and j.name = ‘Job_name’
ORDER BY run_date desc, run_time desc

相关问题