Log in  ::  Register  ::  Not logged in

## Producing dates from SYSJOBS and SYSJOBHISTORY

 Author Message nplace6530 Right there with Babe Group: General Forum Members Points: 770 Visits: 887 Hi allI recently decided to start to report on durations of jobs that I have scheduled on my SQL 2005 system. Rather than reinvent the wheel I thought a script would have been written and posted on numerous forums.After a few searches I found many different ways to pull the information I needed from the SYSJOBS and SYSJOBHISTORY tables. Most scripts created the dates required using various string manipulation methods.One script caught my eye that seemed more elegant than most:http://www.dbforums.com/archive/index.php/t-630647.html`selectconvert(datetime,rtrim(run_date))+ (run_time*9+run_time%10000*6+run_time%100*10+25*du ration)/216e4from msdb..sysjobhistory`While this script gave me the result I required it caused more questions.When converting an INT field (Run_Date) to DATETIME one requires a RTRIM otherwise the conversion produces an error?How does the above formula work? I understand the modulo (%) parts of the calculation being used to find the minutes and seconds part of the run_time field but why multiply them by 6 and 10?Why would you multiply run_time by 9 at the start of the calculation?Why would you multiply run_duration by 25?What’s the significance of the 216e4 (2160000) value? I suspect the calculation is trying to calculate the total run time value in milliseconds perhaps, so is the 216e4 value supposed to be the number of milliseconds in a day? Surely that should be 1000*60*60*24 = 864e5 (86400000)?Any pointers as to how the above formula works would be greatly appreciated.Many thanks.Nick. nplace6530 Right there with Babe Group: General Forum Members Points: 770 Visits: 887 So anyone have any thoughts on the above post? Timothy J Hartford SSCrazy Group: General Forum Members Points: 2390 Visits: 502 Can you please clarify what your after? Are you trying to get a report of start and end times? Average durations? What?Thanks,MentalWhiteNoise nplace6530 Right there with Babe Group: General Forum Members Points: 770 Visits: 887 HiThanks for the reply.The Formula is supposed to produce a date and time of how long the job took to run using the run_date, run_time and duration fields. Timothy J Hartford SSCrazy Group: General Forum Members Points: 2390 Visits: 502 Okay, I am having trouble understanding the why of your reference code as well, so here is what I did:`SELECT run_time , run_time/10000 AS run_time_hours , (run_time%10000)/100 AS run_time_minutes , (run_time%10000)%100 AS run_time_seconds , (run_time/10000 /*run_time_hours*/ * 60 * 60 /* hours to minutes to seconds*/) + ((run_time%10000)/100 /* run_time_minutes */ * 60 /* minutes to seconds */ ) + (run_time%10000)%100 AS run_time_elapsed_seconds , CONVERT(DATETIME, RTRIM(run_date)) AS Start_Date , CONVERT(DATETIME, RTRIM(run_date)) + ((run_time/10000 * 3600) + ((run_time%10000)/100*60) + (run_time%10000)%100 /*run_time_elapsed_seconds*/) / (23.999999*3600 /* seconds in a day*/) AS Start_DateTime , ((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100 /*run_duration_elapsed_seconds*/), CONVERT(DATETIME, RTRIM(run_date)) + ((run_time/10000 * 3600) + ((run_time%10000)/100*60) + (run_time%10000)%100) / (86399.9964 /* Start Date Time */) + ((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100 /*run_duration_elapsed_seconds*/) / (86399.9964 /* seconds in a day*/) AS End_DateTimeFROM msdb.dbo.sysjobhistory`Hopefully this shows my work.FYI, the 23.999999 is required to get to the correct precision of seconds.After doing this, I am getting a factor of 2.777E-4 versus a factor of 2.893E-4 in your current code. This seems to me to be a difference of rounding. The one thing worth noting is that my solution involves taking the modulus of a modulus, versus the other solution which does not.While I know this doesn't answer your question, I hope this helps get to an efficient solution! SwePeso SSC-Insane Group: General Forum Members Points: 22327 Visits: 3433 `DECLARE @d INT, @t INTSELECT @d = 20080504, -- May 4, 2008 @t = 42412 -- 4:24:12 AMSELECT CAST(STR(@d, 8, 0) AS DATETIME), CAST(STUFF(STUFF(STR(@t, 6, 0), 3, 0, ':'), 6, 0, ':') AS DATETIME)` N 56°04'39.16"E 12°55'05.25" nplace6530 Right there with Babe Group: General Forum Members Points: 770 Visits: 887 Many thanks for the reply’s.MentalWhiteNoise and Peso:I have used both these methods to get the data that I require.What I was after in this post was to try and work out how the example I posted worked.After searching through various forums I found a number of ways to calculate the dates and time I required. I came across the example code that I posted on various forums and just couldn't work out how it was supposed to work. msuarez SSC Veteran Group: General Forum Members Points: 208 Visits: 70 nplace6530 (7/29/2008)Hi allI recently decided to start to report on durations of jobs that I have scheduled on my SQL 2005 system. Rather than reinvent the wheel I thought a script would have been written and posted on numerous forums.After a few searches I found many different ways to pull the information I needed from the SYSJOBS and SYSJOBHISTORY tables. Most scripts created the dates required using various string manipulation methods.One script caught my eye that seemed more elegant than most:http://www.dbforums.com/archive/index.php/t-630647.html`selectconvert(datetime,rtrim(run_date))+ (run_time*9+run_time%10000*6+run_time%100*10+25*du ration)/216e4from msdb..sysjobhistory`While this script gave me the result I required it caused more questions.When converting an INT field (Run_Date) to DATETIME one requires a RTRIM otherwise the conversion produces an error?How does the above formula work? I understand the modulo (%) parts of the calculation being used to find the minutes and seconds part of the run_time field but why multiply them by 6 and 10?Why would you multiply run_time by 9 at the start of the calculation?Why would you multiply run_duration by 25?What’s the significance of the 216e4 (2160000) value? I suspect the calculation is trying to calculate the total run time value in milliseconds perhaps, so is the 216e4 value supposed to be the number of milliseconds in a day? Surely that should be 1000*60*60*24 = 864e5 (86400000)?Any pointers as to how the above formula works would be greatly appreciated.Many thanks.Nick.Basically, whoever wrote this query was on a mission to use as few characters as possible.When converting an INT field (Run_Date) to DATETIME one requires a RTRIM otherwise the conversion produces an error?convert(datetime,rtrim(run_date)) would be written more appropriately as:convert(datetime,cast(run_date as varchar))you can't convert from int to datetime. you must first convert from int to varchar, then varchar to datetime. rather than explicitly converting from int to varchar, the author uses the rtrim function. rtrim removes empty white space from the end of a string, which doesnt even exist here.. rtrim expects a varchar, but when it is passed an int, it implicitly converts from int to varchar. so the author isn't using rtrim to actually trim anything, but for the implicit conversion. it's very unclear, but i guess it uses less letters than cast as varchar.field but why multiply them by 6 and 10?Why would you multiply run_time by 9 at the start of the calculation?Why would you multiply run_duration by 25?What’s the significance of the 216e4 (2160000) value? So the author is multiplying the whole number by 9, the last 4 digits by 6, and the last 2 digits by 10...in essence, the author is multiplying the first 2 digits by 90000 (9 x 10000), the middle 2 digits by 1500 ((9 + 6) x 100) and the last 2 digits by 25 ((9 + 6 + 10) x 1)The ratio 90000:1500:25 is the same ratio as 3600:60:1... just multiplied by 25.The author could have replaced 9 with .3600, 6 with .24 (.60 - .36) and 10 with .4 (1 - .6).If the author did that, then the result would only have to be divided by 84600 instead of 216e4 (25 times 86400)(btw... 86400 is 3600 seconds in an hour x 24 hours in a day)so, (run_time*9+run_time%10000*6+run_time%100*10+25*run_duration)/216e4 could be rewritten as:(run_time*.36+run_time%10000*.24+run_time%100*.4+run_duration)/86400the problem is that:(run_time*.36+run_time%10000*.24+run_time%100*.4+run_duration)/86400 doesnt yield as many significant digits, so you would have to cast all of the run_time modulo expressions as floats as such:(cast(run_time as float)*.36+cast(run_time%10000 as float)*.24+cast(run_time%100 as float)*.4+run_duration)/86400All in all, here is a more clearly query, which produces the same result:selectconvert(datetime,cast(run_date as varchar))+(cast(run_time as float)*(.36)+cast(run_time%10000 as float)*(.6-.36)+cast(run_time%100 as float)*(1-.6)+run_duration)/86400from msdb..sysjobhistoryBut again, the author is using as few characters as possible...Hopefully that breaks it down enough for you.-Mike bcurnow SSC-Enthusiastic Group: General Forum Members Points: 107 Visits: 7 I've been chasing this issue as well, and stumbled across the same code that the original poster found. Unfortunately, that code, which I traced back as early as 2002, does not appear to work... at least not in SQL 2005.Given a run_date of 20090112, a run_time of 190239 and a run_duration of 2210, you'd expect the code in the original post to produce 2009-01-12 19:24:49.000. Instead it produces 2009-01-12 19:39:29.000, which is exactly what you'd get when you add 2,210 seconds to a time instead of adding 22 minutes and 10 seconds. I consulted BOL and sure enough, run_duration is an integer representation of time in HHMMSS format, and not an integer containing the number of seconds it took for a job to run.Here's the code I originally found (link):`endTime = DATEADD ( SECOND, jh.run_duration, CAST ( CONVERT(VARCHAR, jh.run_date) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR,jh.run_time),6),5,0,':'),3,0,':') AS DATETIME ) ) `By my eye it's a little easier to read than the code from the original post, although you'll see that the link includes that code as well. This code has the same flaw the the code from the OP does as well. Here's my modification. For formatting style isn't what I use, but I wanted to keep this as close as possible to the original post.`endTime = CAST ( CONVERT(VARCHAR, jh.run_date) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR,jh.run_time),6),5,0,':'),3,0,':') AS DATETIME) + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR,jh.run_duration),6),5,0,':'),3,0,':') `Maybe I've got this whole thing wrong and I've simply overlooked something, but it honestly appears to me that the same example code we've all been using is just flat-out wrong.Edit: Replaced "history." with "jh." in my example to align with rest of code. SwePeso SSC-Insane Group: General Forum Members Points: 22327 Visits: 3433 Will not work for "small" times, as 115 (00:01:15)Use this`DECLARE @rundate INT, @runtime INT, @duration INTSELECT @rundate = 20080504, -- May 4, 2008 @runtime = 42412, -- 4:24:12 AM, @duration = 115 -- 00:01:15SELECT rundate, runtime, duration, rundate + runtime AS start, rundate + runtime + duration AS finishFROM ( SELECT CAST(STR(@rundate, 8, 0) AS DATETIME) AS rundate, CAST(STUFF(STUFF(REPLACE(STR(@runtime, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS DATETIME) AS runtime, CAST(STUFF(STUFF(REPLACE(STR(@duration, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS DATETIME) AS duration ) AS d` N 56°04'39.16"E 12°55'05.25"