Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Producing dates from SYSJOBS and SYSJOBHISTORY Expand / Collapse
Author
Message
Posted Tuesday, July 29, 2008 6:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:34 AM
Points: 264, Visits: 821
Hi all

I 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


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.
Post #542581
Posted Tuesday, August 5, 2008 7:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:34 AM
Points: 264, Visits: 821
So anyone have any thoughts on the above post?
Post #546733
Posted Wednesday, August 6, 2008 9:47 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 22, 2012 8:02 AM
Points: 242, 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
Post #547661
Posted Friday, August 8, 2008 7:24 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:34 AM
Points: 264, Visits: 821
Hi

Thanks 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.


Post #549143
Posted Friday, August 8, 2008 10:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 22, 2012 8:02 AM
Points: 242, 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_DateTime
FROM 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!
Post #549401
Posted Saturday, August 9, 2008 2:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 3:35 PM
Points: 2,393, Visits: 3,399
DECLARE	@d INT,
@t INT

SELECT @d = 20080504, -- May 4, 2008
@t = 42412 -- 4:24:12 AM

SELECT 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"
Post #549728
Posted Monday, August 11, 2008 5:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:34 AM
Points: 264, Visits: 821
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.


Post #550123
Posted Thursday, September 4, 2008 10:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, January 13, 2011 2:58 PM
Points: 64, Visits: 70
nplace6530 (7/29/2008)
Hi all

I 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


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
Post #563999
Posted Tuesday, January 13, 2009 1:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 26, 2012 7:28 AM
Points: 3, 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.



Post #635813
Posted Tuesday, January 13, 2009 2:56 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 3:35 PM
Points: 2,393, Visits: 3,399
Will not work for "small" times, as 115 (00:01:15)
Use this

DECLARE	@rundate INT,
@runtime INT,
@duration INT

SELECT @rundate = 20080504, -- May 4, 2008
@runtime = 42412, -- 4:24:12 AM,
@duration = 115 -- 00:01:15

SELECT rundate,
runtime,
duration,
rundate + runtime AS start,
rundate + runtime + duration AS finish
FROM (
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"
Post #635860
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse