SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Producing dates from SYSJOBS and SYSJOBHISTORY


Producing dates from SYSJOBS and SYSJOBHISTORY

Author
Message
nplace6530
nplace6530
SSChasing Mays
SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)

Group: General Forum Members
Points: 602 Visits: 861
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.
nplace6530
nplace6530
SSChasing Mays
SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)

Group: General Forum Members
Points: 602 Visits: 861
So anyone have any thoughts on the above post?
Timothy J Hartford
Timothy J Hartford
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1666 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
nplace6530
SSChasing Mays
SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)

Group: General Forum Members
Points: 602 Visits: 861
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.
Timothy J Hartford
Timothy J Hartford
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1666 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!
SwePeso
SwePeso
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15173 Visits: 3433

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"
nplace6530
nplace6530
SSChasing Mays
SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)

Group: General Forum Members
Points: 602 Visits: 861
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
msuarez
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 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
bcurnow
bcurnow
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 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
SwePeso
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15173 Visits: 3433
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"
Go


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

































































































































































SQLServerCentral


Search