I've developed a stored procedure to send custom emails and I want to display total duration time of the job into email but...as long my stored procedure which sends emails is last step of the job, I think I can't get a total duration time of the job while it wasn't completed.
So, I tried to SUM all steps duration time but when I SUM 00:01:37 and 00:01:36 values I will have 00:02:70!
Converting last_run_duration value (int) to varchar I used this formula:
STUFF(STUFF(RIGHT('000000' + CAST(last_run_duration AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
How to convert 00:02:70 to 00:03:10 in this case or any other case such 70 minutes to 1 hour and 10 minutes and so on?
Or do you have any idea how to retrieve SQL job duration time without been finished?