Thanks for the article, I always enjoy exploring new ways to tackle persistent challenges.
I particularly like that it handles variable length run_duration values.
Is there a performance gain using the math approach vs the string manipulation?
I'm novice to PowerShell so I don't have experience benchmaking its performance. I'm curious what the performance would be compared to the conventional method converting the time at extraction using string functions.
How can the PowerShell option be applied as a function?
(forgive the novice, the only way I've called PowerShell in T-SQL was through xp_cmdshell)
And does it serve as a performant alternative to the string manipulation (see example)?
I'm aware the below example will fail when the duration exceeds 99 hours, but if a job is running for 99 hours in my environment there are bigger issues than being able to return the time in HH:MM:SS format. So handling a variable length run_duration is not a good use of resources for my environment.
stuff(stuff(left('000000',6-len(run_duration))+cast(run_duration AS VARCHAR),5,0,':'),3,0,':')
FROM msdb.dbo.sysjobhistory WITH(NOLOCK)
(No column name)run_durationinstance_idjob_id
Thoughts? Tips on benchmarking PS performance?
I had an oversight... the point in the article is to return the duration in seconds. However what I posted above only returns formatted time, for duration in seconds I should have posted this:
datediff(ss,0,cast(stuff(stuff(left('000000',6-len(run_duration))+cast(run_duration AS VARCHAR),5,0,':'),3,0,':') AS DATETIME))