Log in  ::  Register  ::  Not logged in

## Working with SQL Agent Durations

 Author Message Kyle Neier , SSCommitted Group: General Forum Members Points: 1858 Visits: 1188 Comments posted to this topic are about the item Working with SQL Agent Durations Marc Kuppens SSC-Enthusiastic Group: General Forum Members Points: 110 Visits: 350 Hi Kyle,at the end of your article you state : "There is not an equivalent to Truncate in T-SQL"Maybe I missed something but what's wrong with the round function (with the third argument <> 0)?try :declare @t1 decimalset @t1 = 23456 select 'using round(?,0) ', round((@t1 / 100),0) as [23456/100], round((-@t1 / 100),0,0) as [-23456/100]union allselect 'using round(?,0,1)', round((@t1 / 100),0,1)as [23456/100], round((-@t1 / 100),0,1)as [-23456/100]union allselect 'using floor ', floor((@t1 / 100))as [23456/100], floor((-@t1 / 100))as [-23456/100]Did I miss something?Marc jennym SSC Veteran Group: General Forum Members Points: 279 Visits: 72 Interesting and good to know! I come from a functional background, and when I encountered this did not know it was a 'standard' way to do things. At the time I was much stronger in Crystal than straight TSQL, and did parse & concatonate as you mention. Ended up with the below to get user friendly times. Obviously this only worked because the value was a time, not a duration. Had it been a duration I would have been one of those you mention who put something together that "fell down" for anything over 24 hours. Thanks for an improved way to approach this!!!select {tblScheduleDetail.StartTime}Case 0:"12:00 AM"Case 1 to 999:"12:0"&left(cstr({tblScheduleDetail.StartTime}),1)&" AM"Case 1000 to 9999:"12:"&left(cstr({tblScheduleDetail.StartTime}),1)& mid(cstr({tblScheduleDetail.StartTime}),3,1)&" AM"Case 10000 to 99999:left(cstr({tblScheduleDetail.StartTime}),1)&":"& mid(cstr({tblScheduleDetail.StartTime}),2,1)&mid(cstr({tblScheduleDetail.StartTime}),4,1)&" AM"case 100000 to 119999:left(cstr({tblScheduleDetail.StartTime}),2)&":"& mid(cstr({tblScheduleDetail.StartTime}),3,1)&mid(cstr({tblScheduleDetail.StartTime}),5,1)&" AM"case 120000 to 240000:left(cstr({tblScheduleDetail.StartTime}),2)&":"& mid(cstr({tblScheduleDetail.StartTime}),3,1)&mid(cstr({tblScheduleDetail.StartTime}),5,1)&" PM";; Rich Mechaber SSCrazy Eights Group: General Forum Members Points: 8577 Visits: 3700 I confess this bit me hard in the #\$% a while ago, twice: once when I found out that date and time were stored as an INT representation, once again when code failed b/c I forgot there could be a zero value for time with only 1 digit.I won't present the following code I developed as a great solution, but b/c it's admin. code that runs but once a day as part of maintenance, I don't really care about performance. It works, and that was sufficient:` SELECT j.name AS JobName, -- NVARCHAR(128) jh.step_id, -- INT jh.step_name, -- NVARCHAR(128) jh.sql_message_id, -- INT jh.sql_severity, -- INT -- sysjobhistory natively stores run_date and run_time as separate integers. Combine and convert to DATETIME. Why MS, why?? CAST ( -- Date portion, which will always be an 8-digit INT in the form yyyymmdd: CAST(jh.run_date AS VARCHAR(8)) + ' ' + -- Time portion is harder, b/c it can be 0, nnnnn (5 digits), or nnnnnn (6 digits) in the form hmmss. No leading zero. -- This construct will prepend 6 zeroes, then take the rightmost 6 characters, yielding a 6-character string: -- RIGHT('000000' + CAST(run_time AS VARCHAR(6)), 6) -- We then slice and re-format to hh:mm:ss and combine with the date, then cast the whole shebang as DATETIME. LEFT(RIGHT('000000' + CAST(jh.run_time AS VARCHAR(6)), 6), 2) + ':' + Substring(RIGHT('000000' + CAST(jh.run_time AS VARCHAR(6)), 6), 3, 2) + ':' + RIGHT(RIGHT('000000' + CAST(jh.run_time AS VARCHAR(6)), 6), 2) AS DATETIME) As RunDateTime, jh.message, -- NVARCHAR(1024) jh.run_status, -- INT jh.run_duration -- INT INTO #t FROM MSDB.dbo.sysjobs j INNER JOIN MSDB.dbo.sysjobhistory jh ON j.job_id = jh.job_id WHERE jh.sql_severity > 0 OR jh.run_status = 0`Rich Michael.Beeby SSC Eights! Group: General Forum Members Points: 819 Visits: 706 I did wonder about STUFF, e.g.SELECT STUFF ('200', 2 , 0, ':')DECLARE @MJBtime INTSET @MJBtime = '10200'SELECT LEN(@MJBtime)SELECT STUFF (@MJBtime, LEN(@MJBtime)-1, 0, ':') --Not 2 as 1st value before first characterSELECT STUFF(STUFF (@MJBtime, LEN(@MJBtime)-1, 0, ':'),LEN(@MJBtime)-3,0,':') Kyle Neier , SSCommitted Group: General Forum Members Points: 1858 Visits: 1188 Marc ~I don't believe you are missing anything. That seems to be a perfectly viable alternative - one that I was unaware of until today.Thanks for sharing! SQL-Tucker SSC-Enthusiastic Group: General Forum Members Points: 150 Visits: 31 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.`SELECT stuff(stuff(left('000000',6-len(run_duration))+cast(run_duration AS VARCHAR),5,0,':'),3,0,':') ,run_duration ,*FROM msdb.dbo.sysjobhistory WITH(NOLOCK)result sample(No column name) run_duration instance_id job_id00:00:02 2 12723 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:02 2 12724 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:02 2 12725 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:02 2 12726 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:02 2 12727 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:02 2 12728 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:02 2 12729 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:03 3 12730 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:10 10 12731 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:11 11 12732 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:25:34 2534 12733 95BD60FC-DF27-45A1-AAC4-7CA4EA90442E00:25:35 2535 12734 95BD60FC-DF27-45A1-AAC4-7CA4EA90442E`Thoughts? Tips on benchmarking PS performance?[update]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))` Rich Mechaber SSCrazy Eights Group: General Forum Members Points: 8577 Visits: 3700 SQL-Tucker (8/23/2012)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.`SELECT stuff(stuff(left('000000',6-len(run_duration))+cast(run_duration AS VARCHAR),5,0,':'),3,0,':') ,run_duration ,*FROM msdb.dbo.sysjobhistory WITH(NOLOCK)result sample(No column name) run_duration instance_id job_id00:00:02 2 12723 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:02 2 12724 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:02 2 12725 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:02 2 12726 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:02 2 12727 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:02 2 12728 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:02 2 12729 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:03 3 12730 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:10 10 12731 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:00:11 11 12732 D4C68B9E-C8BF-4B71-ADE6-062BA0014D7800:25:34 2534 12733 95BD60FC-DF27-45A1-AAC4-7CA4EA90442E00:25:35 2535 12734 95BD60FC-DF27-45A1-AAC4-7CA4EA90442E`Thoughts? Tips on benchmarking PS performance?That code errors out on my trial:`Msg 536, Level 16, State 5, Line 1Invalid length parameter passed to the SUBSTRING function.`Rich Jason- SSCommitted Group: General Forum Members Points: 1993 Visits: 545 rmechaber (8/23/2012)That code errors out on my trial:`Msg 536, Level 16, State 5, Line 1Invalid length parameter passed to the SUBSTRING function.`RichI've seen this before. There is a negative value in your duration ( like -954448987) While I don't know what causes this (maybe a SQL Agent bug) it is the reason for failing as the value is more than 6 characters. clear your agent history to get rid of the negative value or add a where clause "WHERE run_duration > 0"EDIT: Could also be a duration greater than 99 hours. In which case the where clause would be "WHERE run_duration BETWEEN 0 and 999999" - Rich Mechaber SSCrazy Eights Group: General Forum Members Points: 8577 Visits: 3700 Jason- (8/23/2012)rmechaber (8/23/2012)That code errors out on my trial:`Msg 536, Level 16, State 5, Line 1Invalid length parameter passed to the SUBSTRING function.`RichI've seen this before. There is a negative value in your duration ( like -954448987) While I don't know what causes this (maybe a SQL Agent bug) it is the reason for failing as the value is more than 6 characters. clear your agent history to get rid of the negative value or add a where clause "WHERE run_duration > 0"Good call, that was the issue. Why the Agent log has a negative run duration, I don't know; Googling only turned up more "me, too *shrugs*" posts, without an explanation. If anyone here has an answer, that would be nice. The job in question was an Index rebuild subplan in a maintanance plan.Rich

## Permissions

 You can't post new topics. You can't post topic replies. You can't post new polls. You can't post replies to polls. You can't edit your own topics. You can't delete your own topics. You can't edit other topics. You can't delete other topics. You can't edit your own posts. You can't edit other posts. You can't delete your own posts. You can't delete other posts. You can't post events. You can't edit your own events. You can't edit other events. You can't delete your own events. You can't delete other events. You can't send private messages. You can't send emails. You can read topics. You can't vote in polls. You can't upload attachments. You can download attachments. You can't post HTML code. You can't edit HTML code. You can't post IFCode. You can't post JavaScript. You can post emoticons. You can't post or upload images.

##### Select a forum
 SQL Server 2017      SQL Server 2017 - Administration      SQL Server 2017 - Development SQL Server 2016      SQL Server 2016 - Administration      SQL Server 2016 - Development and T-SQL SQL Server 2014      Administration - SQL Server 2014      Development - SQL Server 2014 SQL Server 2012      SQL 2012 - General      SQL Server 2012 - T-SQL SQL Server vNext      SQL Server 15 - Administration      SQL Server 15 - Development SQL Server 2008      SQL Server 2008 - General      T-SQL (SS2K8)      June 2007 CTP      Working with Oracle      July CTP      SQL Server Newbies      Security (SS2K8)      SQL Server 2008 High Availability      SQL Server 2008 Administration      Data Corruption (SS2K8 / SS2K8 R2)      SQL Server 2008 Performance Tuning Cloud Computing      SQL Azure - Development      SQL Azure - Administration      Amazon AWS and other cloud vendors      General Cloud Computing Questions      CosmosDB      Azure Data Lake      Azure Machine Learning Reporting Services      Reporting Services      Reporting Services 2005 Administration      Reporting Services 2005 Development      Reporting Services 2008/R2 Administration      Reporting Services 2008 Development      SSRS 2012      SSRS 2014      SSRS 2016 Programming      Connecting      General      SMO/RMO/DMO      XML      Service Broker      Powershell      Testing      TFS/Data Dude/DBPro      SSDT      Continuous Integration, Deployment, and Delivery      R Services and R Language Data Warehousing      Integration Services      Strategies and Ideas      Analysis Services      Data Transformation Services (DTS)      Performance Point      Data Mining      PowerPivot      R language      Machine Learning Database Design      Disaster Recovery      Design Ideas and Questions      Relational Theory      Hardware      Virtualization      Security and Auditing SQLServerCentral.com      Anything that is NOT about SQL!      Contests!      Editorials      SQLServerCentral.com Announcements      SQLServerCentral.com Website Issues      Suggestions      Tag Issues with Content      Podcast Feedback      SQLServerCentral.com Test Forum      Articles Requested SQL Server 2005      Administering      Backups      Business Intelligence      CLR Integration and Programming.      Data Corruption      Development      Working with Oracle      SQL Server 2005 Compact Edition      SQL Server 2005 General Discussion      SQL Server 2005 Security      SQL Server 2005 Strategies      SS2K5 Replication      SQL Server Express      SQL Server 2005 Performance Tuning      SQL Server 2005 Integration Services      T-SQL (SS2K5)      SQL Server Newbies SQL Server 7,2000      Administration      Backups      Data Corruption      General      Globalization      In The Enterprise      Working with Oracle      Security      Strategies      SQL Server Newbies      Service Packs      SQL Server CE      Performance Tuning      Replication      Sarbanes-Oxley      T-SQL      SQL Server Agent SQL Server and other platforms      MySQL      Oracle      PostgreSQL      DB2      SQL Server and Sharepoint Older Versions of SQL (v6.5, v6.0, v4.2)      Older Versions of SQL (v6.5, v6.0, v4.2) Career      Certification      Employers and Employees      Events      Job Postings      Resumes and Job Hunters      Presentations and Speaking      Retired Members Testing Center      Question of the Day (QOD)      SQL Server Security Skills Microsoft Access      Microsoft Access Products and Books      Third Party Products         CA         SQLCentric         Extreme Technologies.         Innovartis         Embarcadero         Sonasoft         Golden Gate Software         Idera         Lumigent         Red Gate Software         Quest Software         ApexSQL         SQL Sentry      Discussions about Books         Discuss Programming Books          Discuss XML Books          Discuss T-SQL Books          Discuss DTS Books          Discuss SQL Server 2000 Books         Discuss SQL Server 7.0 Books         Discuss Data Warehousing Books  Notification Services      Administration Article Discussions Future Versions      SQL 12