August 30, 2012 at 4:25 am
+5 cent's of mine
select top 100
name
,
convert(datetime,(convert(varchar(8),h.run_date)))
+(
+substring (right( '000000'+(convert (varchar (6), h.run_time) ),6),1,2)
+':'+substring (right( '000000'+(convert (varchar (6), h.run_time) ),6),3,2)
+':'+substring (right( '000000'+(convert (varchar (6), h.run_time) ),6),5,2)
)
begining
,
convert (varchar ,convert(int,reverse (substring (reverse (convert (varchar , run_duration)),5,4))))+
+':'+substring (right( '000000'+(convert (varchar , run_duration) ),6),3,2)
+':'+substring (right( '000000'+(convert (varchar , run_duration) ),6),5,2)
duration
,
-- EdsTime as begining+duration
convert( datetime, convert(varchar(8),h.run_date))
+ (
substring (right( '000000'+(convert (varchar (6), h.run_time) ),6),1,2)
+':'+substring (right( '000000'+(convert (varchar (6), h.run_time) ),6),3,2)
+':'+substring (right( '000000'+(convert (varchar (6), h.run_time) ),6),5,2)
)
+
( -- add hours
dateadd (hh, convert(int,reverse (substring (reverse (convert (varchar , run_duration)),5,4))
), 0)
+
-- add mins
dateadd (mi, convert (int,
substring (right( '000000'+(convert (varchar , run_duration) ),6),3,2)
), 0)
+
-- add seconds
dateadd (ss, convert (int,
substring (right( '000000'+(convert (varchar , run_duration) ),6),5,2)
), 0)
)
endtime
from msdb..sysjobhistory h with( nolock), msdb..sysjobs j with( nolock)
where h.step_id=0 and h.job_id=j.job_id order by instance_id desc
works for several years to make this:
December 25, 2012 at 8:46 am
Good article.
Viewing 2 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy