March 13, 2006 at 9:58 am
I haven't posted very much and I apologize in advance in case I do this wrong - but I am going nuts. I am using a bit of sql I found several places on the internet to calculate the end time for jobs in sysjobhistory. It gives me the wrong answer. It seems obvious that if a job starts at 6:05 and runs for 14 minutes, the end time should be something like 6:19. This routine gives me a time of 6:28. (That's double the run time but results are not consistent.)
Here is the code and a few rows of the result. I am wondering if something has changed since this sql was devised. Any ideas?
select job_name = sj.name, run_date, run_time, run_duration, endTime = CONVERT
(
DATETIME,
RTRIM(run_date)
)
+
(
run_time * 9
+ run_time % 10000 * 6
+ run_time % 100 * 10
+ 25 * run_duration
) / 216e4
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.sysjobhistory sjh
ON sj.job_id = sjh.job_id
WHERE sj.name like '%Backup%'
ORDER by run_date DESC
DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1'2006031018050014243/10/2006 6:28:44.000 PM
DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1'2006031018050014243/10/2006 6:28:44.000 PM
DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1'2006030918050014013/9/2006 6:28:21.000 PM
DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1'2006030918050014023/9/2006 6:28:22.000 PM
DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1'2006030818050014023/8/2006 6:28:22.000 PM
DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1'2006030818050014023/8/2006 6:28:22.000 PM
DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1'2006030718050014053/7/2006 6:28:25.000 PM
DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1'2006030718050014053/7/2006 6:28:25.000 PM
DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1'2006030618050013583/6/2006 6:27:38.000 PM
DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan1'2006030618050013583/6/2006 6:27:38
March 13, 2006 at 10:04 am
Try this script:
use msdb
go
select
left(b.run_date,4)+'/'+right(left(b.run_date,6),2)+'/'+right(b.run_date,2) as run_date,
a.name,
b.step_id,
b.step_name,
convert(varchar,(convert(datetime,(cast((CASE
WHEN len(b.run_time)=6 THEN LEFT(b.run_time,2)
WHEN len(b.run_time)=5 THEN LEFT(b.run_time,1)
ELSE 0
END) AS VARCHAR(2))+':'+
cast((CASE
WHEN len(b.run_time)>=4 THEN LEFT(RIGHT(b.run_time,4),2)
WHEN len(b.run_time)=3 THEN LEFT(b.run_time,1)
ELSE 0
END) AS VARCHAR(2))+':'+RIGHT(b.run_time,2)))),108) as start_time,
Convert(varchar,
(
convert(datetime,(cast((CASE
WHEN len(run_time)=6 THEN LEFT(run_time,2)
WHEN len(run_time)=5 THEN LEFT(run_time,1)
ELSE 0
END
) AS VARCHAR(2))+':'+
cast((CASE
WHEN len(run_time)>=4 THEN LEFT(RIGHT(run_time,4),2)
WHEN len(run_time)=3 THEN LEFT(run_time,1)
ELSE 0
END) AS VARCHAR(2))+':'+RIGHT(run_time,2)))
+
convert(datetime,(cast((CASE
WHEN len(run_duration)=6 THEN LEFT(run_duration,2)
WHEN len(run_duration)=5 THEN LEFT(run_duration,1)
ELSE 0
END
) AS VARCHAR(2))+':'+
cast((CASE
WHEN len(run_duration)>=4 THEN LEFT(RIGHT(run_duration,4),2)
WHEN len(run_duration)=3 THEN LEFT(run_duration,1)
ELSE 0
END) AS VARCHAR(2))+':'+RIGHT(run_duration,2)))
),108) as end_time
from sysjobs a JOIN sysjobhistory b ON a.job_id=b.job_id
WHERE
(
(b.run_time<=100000 AND b.run_date=convert(varchar,getdate(),112))
OR
(b.run_time>100000 AND b.run_date=convert(varchar,(dateadd(dd,-1,getdate())),112))
)
AND
b.step_name<>'(Job outcome)'
order by end_time
March 13, 2006 at 10:35 am
This does give me the right answer which is a definite improvement! I will want to study it a bit so I can figure out what it's doing.
But if anyone has any insight on why the other routine produces a wrong answer, I'd be interested to know. I produced a report for a client with wrong times on it and I can't help wondering why.
But thank you for the answer - I will use this as a basis for my script. It will save me a lot of time.
March 13, 2006 at 10:48 am
I am not sure what is the logic of the script that you posted . The one I posted has a lot of code, but the logic is simple:
take two right most digits and let that be seconds,
take one or two, depending on overall legth, following digits and let that be minutes
take one or two, depending on overall length, leftmost digits and let that be hours.
Now put that together into a datetime format and and things up as appropriate.
March 14, 2006 at 8:07 am
Judy,
If you noticed how Mordechai used the convert and cast functions in his script to get the accurate calculations of time. The sysjobhistory datatypes for their run_time and run_date fields are int, so when you try to do just basic math calculations on this data, it does not calculate accurately unless you convert it to datetime.
Jules Bui
IT Operations DBA
Backup and Restore Administrator
March 14, 2006 at 8:18 am
Or try this function:
set
ANSI_NULLS ON
set
QUOTED_IDENTIFIER ON
go
/****** Object: User Defined Function dbo.fn_AgentCompletionDateTime Script Date: 8/11/2005 2:31:03 PM ******/
Create
function [dbo].[fn_AgentCompletionDateTime] (@agentdate int, @agenttime int, @agentduration int)
returns
datetime
as
begin
declare @date datetime,
@year
int,
@month
int,
@day
int,
@hour
int,
int,
int,
@datestr
nvarchar(40),
@runduration
int
select @year = (@agentdate / 10000)
select @month = (@agentdate - (@year * 10000)) / 100
select @day = (@agentdate - (@year * 10000) - (@month * 100))
select @hour = (@agenttime / 10000)
select @min-2 = (@agenttime - (@hour * 10000)) / 100
select @sec = (@agenttime - (@hour * 10000) - (@min-2 * 100))
select
@runduration = case when @agentduration <100 then @agentduration
when @agentduration between 100 and 999 then left(@agentduration,1)*60+right(@agentduration,2)
when @agentduration between 1000 and 9999 then left(@agentduration,2)*60+right(@agentduration,2)
when @agentduration > 9999 then left(@agentduration,len(@agentduration)-4)*3600+left(right(@agentduration,4),2)*60+right(@agentduration,2)
end
select @datestr = convert(varchar(4), @year) + N'-' +
convert(varchar(2), @month) + N'-' +
convert(varchar(4), @day) + N' ' +
replace(convert(varchar(2), @hour) + N':' +
convert(varchar(2), @min-2) + N':' +
convert(varchar(2), @sec), ' ', '0')
select @date = convert(datetime, @datestr)
select @date =dateadd(ss,@runduration,@date)
return @date
end
March 14, 2006 at 8:31 am
I did get the drift of Mordechai's solution but thanks for explaining it so simply. Also for the function which might be a useful way of handling this ultimately. My nephew who is graduating with a degree in math and his father who is also a mathemetician looked over the first script and they think the math is OK but it does seem likely that a pure math solution applied to a date/time problem might not be accurate enough. For the record - 216e4 translates to 216*10 to the 4th which translates to 60*60*60*10 - must have something to do with time.
March 15, 2006 at 7:40 am
The original query you posted seems like an elegant solution. If you have the time, may be you could explain the logic behind this segment:
(
run_time * 9
+ run_time % 10000 * 6
+ run_time % 100 * 10
+ 25 * run_duration
) / 216e4
Obviously it is the one giving inconsistent results, but not knowing what is the math behind it, I don't think I can troubleshoot it.
July 18, 2006 at 12:55 pm
Judy, the reason that the solution you have does not work is because the author assumed that the run_duration field was the number of seconds that the job ran. They were incorrect. the run_duration field is just like the run_time field in that it is in the format HHMMSS. basically, the author is adding the decimal fraction of a day that is represented in run_time to midnight of the day in run_date. he/she calculates this fraction by considering the integer representation of hhmmss as a sexagisemal value. there is a caveat in there that has to do with the fact that there is a difference in order of magnitude but I'm not going there in this post. Bottom line 1 sec = 25/2160000ths of a day. Thats why he/she was multiplying the duration by 25.
try this and see if it works...
(
run_time * 9
+ run_time % 10000 * 6
+ run_time % 100 * 10
+ run_duration * 9
+ run_duration % 10000 * 6
+ run_duration % 100 * 10
) / 216e4
March 21, 2007 at 3:31 pm
I don't know the way to get end time,
but I think, the simpliest way to get starting datetime from sysjobhistory is:
STR(run_date,8)+' '+STUFF(STUFF(RIGHT(1000000+run_time,6),5,0,':'),3,0,':')
or
CONVERT(DATETIME,STR(run_date,8)+' '+STUFF(STUFF(RIGHT(1000000+run_time,6),5,0,':'),3,0,':'))
.
March 22, 2007 at 2:07 am
When run_duration exceeded 100 hours than LEN(run_duration)>6..
The end time could be counted like this:
DATEADD(second,
[run_duration]/10000*3600+[run_duration]%10000/100*60+[run_duration]%100,
STR(run_date,8)+' '+STUFF(STUFF(RIGHT(1000000+run_time,6),5,0,':'),3,0,':'))
January 28, 2020 at 5:44 pm
select top 10000 instance_id, job_name = sj.name, run_date, run_time, run_duration
--,convert(DATETIME,RTRIM(run_date)) as myrundate
--,convert(int,(run_time / 10000)) as run_time_hour
--,convert(int,(run_time / 100))%100 as run_time_minutes
--,convert(int,(run_time / 1))%100 as run_time_seconds
--,convert(int,(run_duration / 1000000)) as run_duration_days
--,convert(int,(run_duration / 10000))%100 as run_duration_hour
--,convert(int,(run_duration / 100))%100 as run_duration_minutes
--,convert(int,(run_duration / 1))%100 as run_duration_seconds
, dateadd(second, ( convert(int,(run_time / 10000)) * 3600 )
+ (convert(int,(run_time / 100))%100) * 60
+ (convert(int,(run_time / 1))%100)
+ ( convert(int,(run_duration / 1000000))) * 3600 * 24
+ (convert(int,(run_duration / 10000))%100) * 3600
+ (convert(int,(run_duration / 100))%100) * 60
+ (convert(int,(run_duration / 1))%100)
,convert(DATETIME,RTRIM(run_date))) as StepCompleteDateTime
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.sysjobhistory sjh
ON sj.job_id = sjh.job_id
-- -- https://vijredblog.wordpress.com/2020/01/27/sql-job-history-getting-end-time-from-sysjobhistory/
-Vijred (http://vijredblog.wordpress.com)
February 26, 2020 at 6:05 pm
There is a built-in SQL function called msdb.dbo.agent_datetime that will convert the run_date and run_time into a datetime. From there it is simply a matter of doing a dateadd of the hours, minutes, and seconds to get the end time of the job. I think the below SQL is a bit more succinct.
select
J.name,
dbo.agent_datetime(H.run_date, H.run_time) start_time,
dateadd(second, run_duration%100, dateadd(minute, run_duration/100%100, dateadd(hour, run_duration/10000, dbo.agent_datetime(H.run_date, H.run_time)))) end_time,
H.run_date,
H.run_time,
run_duration
from msdb.dbo.sysjobs J
inner join msdb.dbo.sysjobhistory H on J.job_id = H.job_id
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply