SQL Agent Jobs; In msdb you have dbo.sysjobhistory who gives you Run_date and Run_time as type INT which easily can convert to Start_Datetime with dbo.agent_datetime. I get: 2018-05-17 01:13:09.240. Run_duration is INT as well. I use STUFF(STUFF(RIGHT('000000' + CAST ( jh.run_duration AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') as 'Time_HH:MM:SS'. I get 27:31:20
I want to calculate and to have a new column as End_datetime. (i.e Start_datetime + Run_duration) My jobs are running more than 24h etc so date can go inte next day as well.
Where to start? After conversion or start with the INT colummns Run_date, Run_time, Run_duration?
Thanks for a good short script!
May I ask one more? The Run_duration column below have as a first example of 122hours and then my conversion with Stuff as mentioned above gives 00:00:00* with the asterix when hours are above 100
May I ask for a Better conversion script?
That's complex enough that you need to add a CROSS APPLY to the original query to calc the run_duration len but with a minimum len of 6. Like this:
SELECT TOP (20) run_duration,
STUFF(STUFF(RIGHT(REPLICATE('0', run_duration_len) + CAST(jh.run_duration AS varchar(10)), run_duration_len),run_duration_len - 1,0,':'),run_duration_len - 3,0,':') AS 'Time_HH:MM:SS'
--FROM ( values(1000010),(1234567),(12345678) ) jh(run_duration)
FROM msdb.dbo.sysjobhistory jh
CROSS APPLY (
SELECT CASE WHEN LEN(jh.run_duration) < 6 THEN 6 ELSE LEN(jh.run_duration) END AS run_duration_len
) AS alias1
By using these lines I did manage to get minutes and seconds when hours are than 99 ie 3 digits. I would be thankful for comments if there is some limitation in my solution (due to me beeing a beginner in SQL)