April 19, 2002 at 8:29 am
Is this in seconds or ? It is not documented in BOL except for amount of time incurred in execution of a job. Thanks ...
April 19, 2002 at 8:48 am
From SQL Server 2000 System Table Map (download from MS - search on systbl.chm)
"Elapsed time in the execution of the job or step in HHMMSS format."
Hope this helps.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
April 19, 2002 at 8:55 am
It is based on time value sections.
Ex.
run_duration = HH:MM:SS
1 = 00:00:01
335 = 00:03:35
102456 = 10:24:56
Hops this helps.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 19, 2002 at 9:43 am
Antares686 Thanks for your assistance on this matter, pochinej from http://www.sql-scripting.com
April 19, 2002 at 10:08 am
DavidB Thanks also, I have the sytbl map and was working sql7 never know if its been changed but seems it has not been, Thanks...
April 7, 2009 at 8:58 am
All,
I hate the formatting myself so thought I would share. This code was "borrowed" from Mike Pearson, thanks Mike...
As you can see it's all in the CASE.
select j.name,
case when h.step_name ='(Job outcome)' then '(Package Total)' else h.step_name end
, h.run_status,
CASE len(h.run_duration)
WHEN 1 THEN cast('00:00:0'
+ cast(h.run_duration as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(h.run_duration as char) as char (8))
WHEN 3 THEN cast('00:0'
+ Left(right(h.run_duration,3),1)
+':' + right(h.run_duration,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(h.run_duration,4),2)
+':' + right(h.run_duration,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(h.run_duration,5),1)
+':' + Left(right(h.run_duration,4),2)
+':' + right(h.run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(h.run_duration,6),2)
+':' + Left(right(h.run_duration,4),2)
+':' + right(h.run_duration,2) as char (8))
END as 'Duration'
,h.run_duration
from dbo.sysjobs j
inner join dbo.sysjobhistory h
on j.job_id=h.job_id
where j.name like '%somejobName%'
order by j.name, h.step_name
December 23, 2010 at 12:48 am
One more solution without case statement found in another forum,
SELECT stuff(stuff(replace(str(run_duration,6,0),' ','0'),3,0,':'),6,0,':') FROM sysJobHistory
October 27, 2011 at 11:54 am
Just curious in a hypothetical extreme situation but what if a job runs beyond a day like say for example 25 hours (or further more weeks, months, years, etc...)?...would it then still be represented as 25 hours or something like 1 day & 1 hour? Thanks in advance.
October 27, 2011 at 2:18 pm
Yes, days can be included on the value. You can test this by creating a job that just sits for a day. I have a messy formula to calculate the value.
I like to deal with it in seconds so I use this:
((jh.run_duration/1000000)*86400) + (((jh.run_duration-((jh.run_duration/1000000)*1000000))/10000)*3600) + (((jh.run_duration-((jh.run_duration/10000)*10000))/100)*60) + (jh.run_duration-(jh.run_duration/100)*100)
Where jh is the jobhistory table..
CEWII
October 27, 2011 at 2:42 pm
Thanks Elliott for the quick reply, much appreciated. So the further left/highest value that the run_duration field would contain would always be displayed in hours then?
October 27, 2011 at 2:48 pm
No, as I remember and my formula suggests a day would be represented as 1000000, this is handled by ((jh.run_duration/1000000)*86400).
I never tested a case where a year had passed and I think it unlikely to happen.
CEWII
July 6, 2023 at 3:11 pm
Apologies for bringing up an ancient thread, but when I wanted the start and end times the simplest solution that I have found (in SQL Server 2019) is to use the FORMAT() function and add it to the start time.
select JobName = j.[name]
,StartTime = msdb.dbo.agent_datetime(run_date, run_time)
,EndTime = msdb.dbo.agent_datetime(run_date, run_time)
+ format(h.run_duration,'00:00:00')
From msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h
ON j.job_id = h.job_id
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply