Calculating time difference in msdb..sysjobhistory

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calculating time difference in msdb..sysjobhistory

Author

Topic

panthagani
Yak Posting Veteran

58 Posts

Posted - 2006-12-28 : 14:27:23

I am setting up a monitor to alert me if an SQL job has failed in the "last 20 minutes". This should run 24 hours a day, 7 days a week. My query looks something like this.

select * from TALMAIN.msdb.dbo.sysjobhistory where job_id = '7139D5D1-CD88-46E8-8324-5D5A0D8D3A27' and run_status <> 1 and
DATEPART(YYYY,GETDATE()) = substring(convert(char(8),run_date),1,4)and
DATEPART(MM,GETDATE()) = substring(convert(char(8),run_date),5,2) and
DATEPART(DD,GETDATE()) = substring(convert(char(8),run_date),7,2)and DATEPART(HH,GETDATE()) = substring(convert(char(8),run_time),1,2)and (DATEPART(MI,GETDATE()) - substring(convert(char(8),run_time),3,2)) <= 20.

The run_date and run_time columns in msdb..sysjobhistory are stored as integers. Tried a couple of things, but I am unable to convert both of them to datetime data type. The last conditions in the above logic hold true for only "2 digit" hour and minute values.

DATEPART(HH,GETDATE()) = substring(convert(char(8),run_time),1,2)and (DATEPART(MI,GETDATE()) - substring(convert(char(8),run_time),3,2)).

What about time values like 00:05 AM and single digit time values like 1:00 AM and 9:05 AM, for example?. I pasted some sample run_date and run_time values from sysjobhistory below.

run_date run_time

20061228 21510 -- 02:15:10 AM (how to get the minute count?)
20061228 21510 -- 02:15:10 AM (same as above)
20061227 233014 -- 23:30:14 PM (this is strt forward)
20061227 233014 -- 23:30:14 PM (same as above)
20061227 3016 -- 00:30:16 AM (how to get minute count?)
20061227 3015 -- 00:30:15 AM (how to get minute count?)

Is there a simpler logic to achieve this? Hope I was clear, else let me know. Please advise. Thank you.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-28 : 15:06:21

You can use integer division and modulo (%) to get what you need, like this to get a datetime for each row

select run_date, run_time, cast(cast(run_date as varchar(8)) + ' ' 
    + cast(run_time / 10000 as varchar(2)) + ':'
    + cast((run_time % 10000) / 100 as varchar(2)) + ':'
    + cast(run_time % 100 as varchar(2)) as datetime) 
from sysjobhistory

Or this to just get the hours, minutes and seconds as integers

select run_time, 
	run_time / 10000 as Hours,
	(run_time % 10000) / 100 as Minutes,
	run_time % 100 as Seconds 
from sysjobhistory

panthagani
Yak Posting Veteran

58 Posts

Posted - 2006-12-28 : 16:07:19

Thank you, snSQL. This works perfectly.

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-12-28 : 23:00:03

Or....


 SELECT *
   FROM TALMAIN.MSDB.dbo.SysJobHistory
  WHERE Job_ID = '7139D5D1-CD88-46E8-8324-5D5A0D8D3A27'
    AND Run_Status <> 1
    AND STR(Run_Date,8) + ' ' 
      + REPLACE(STUFF(STUFF(STR(Run_Time,6),5,0,':'),3,0,':'),' ','0')
     >= DATEADD(mi,-20,GETDATE())

--Jeff Moden

panthagani
Yak Posting Veteran

58 Posts

Posted - 2006-12-29 : 13:32:25

I was working on similar lines with STUFF but got stuck until your post enlightened me. Thanks again, Jeff

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-12-29 : 16:43:15

You bet... thank you for the feedback.

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources