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,':') )
2条答案
按热度按时间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 inrunDuration
, you could express this like:Here is a contrived example:
fiddle
wztqucjr2#
I understand that you want to extract active job that have lasted more than 24 hours
History Job: