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">
http://www.dbforums.com/archive/index.php/t-630647.html
select
convert(datetime,rtrim(run_date))
+ (run_time*9+run_time%10000*6+run_time%100*10+25*du ration)/216e4
from 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)/86400
the 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)/86400
All in all, here is a more clearly query, which produces the same result:
select
convert(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)/86400
from msdb..sysjobhistory
But again, the author is using as few characters as possible...
Hopefully that breaks it down enough for you.
-Mike