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»»

DBO.SysJobHistory, how do you get the Job Time? Expand / Collapse
Author
Message
Posted Tuesday, May 1, 2012 9:44 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 310, Visits: 537
In the MSDB MS is storing the information about a SQL Job, and in the DBO.SysJobHistory it has a column called "run_time".. Using SQL how do you convert this field to get the time the job ran?
Post #1293251
Posted Tuesday, May 1, 2012 10:15 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:57 PM
Points: 23,009, Visits: 31,508
dwilliscp (5/1/2012)
In the MSDB MS is storing the information about a SQL Job, and in the DBO.SysJobHistory it has a column called "run_time".. Using SQL how do you convert this field to get the time the job ran?


Are you asking at what time the job ran or how long the job (or job step) ran?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1293284
Posted Tuesday, May 1, 2012 10:19 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:57 PM
Points: 23,009, Visits: 31,508
If what time, the integer value represents the time the job or job step started in the format hhmmss.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1293285
Posted Tuesday, May 1, 2012 10:25 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 27, 2014 7:41 AM
Points: 739, Visits: 518
Hi Lynn,

I can't remember where I got hold of this script but it does the conversion as you've asked:


SELECT J.[name] JobName
, J.[description] JobDescription
, CONVERT(DATETIME, Cast(S.next_run_date AS VARCHAR), 112) AS NextRunDate
, Datename(dw, CONVERT(DATETIME, Cast(S.next_run_date AS VARCHAR), 112)) AS NextRunDate_Day
, S.next_run_time AS NextRunTime
, (SELECT Min(run_time)
FROM msdb.dbo.sysjobhistory H
WHERE H.job_id = J.job_id) AS Duration_MIN
, (SELECT Avg(run_time)
FROM msdb.dbo.sysjobhistory H
WHERE H.job_id = J.job_id) AS Duration_AVG
, (SELECT Max(run_time)
FROM msdb.dbo.sysjobhistory H
WHERE H.job_id = J.job_id) AS Duration_MAX
FROM msdb.dbo.sysjobs J
LEFT JOIN msdb.dbo.sysjobschedules S
ON J.job_id = S.job_id
WHERE J.[Enabled] = 1


"Be brave. Take risks. Nothing can substitute experience."
Post #1293291
Posted Tuesday, May 1, 2012 10:29 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:57 PM
Points: 23,009, Visits: 31,508
Robin Sasson (5/1/2012)
Hi Lynn,

I can't remember where I got hold of this script but it does the conversion as you've asked:


SELECT J.[name] JobName
, J.[description] JobDescription
, CONVERT(DATETIME, Cast(S.next_run_date AS VARCHAR), 112) AS NextRunDate
, Datename(dw, CONVERT(DATETIME, Cast(S.next_run_date AS VARCHAR), 112)) AS NextRunDate_Day
, S.next_run_time AS NextRunTime
, (SELECT Min(run_time)
FROM msdb.dbo.sysjobhistory H
WHERE H.job_id = J.job_id) AS Duration_MIN
, (SELECT Avg(run_time)
FROM msdb.dbo.sysjobhistory H
WHERE H.job_id = J.job_id) AS Duration_AVG
, (SELECT Max(run_time)
FROM msdb.dbo.sysjobhistory H
WHERE H.job_id = J.job_id) AS Duration_MAX
FROM msdb.dbo.sysjobs J
LEFT JOIN msdb.dbo.sysjobschedules S
ON J.job_id = S.job_id
WHERE J.[Enabled] = 1



???



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1293294
Posted Tuesday, May 1, 2012 11:59 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 310, Visits: 537
SELECT RUN_TIME
FROM MSDB.DBO.SYSJOBHISTORY

I have run_times from 200 up to 230200??? The run_date is YYYYMMDD but how you get the time the job ran from 200 or 230200 I have no idea.
Post #1293368
Posted Friday, May 2, 2014 3:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 2:44 AM
Points: 25, Visits: 576
Trying to answer the same question this morning found this page http://reportingservicestnt.blogspot.co.uk/2012/05/sql-server-failed-jobs-in-ssms.html

Extracted what l wanted from the code, and this query l think answers the question
SELECT  JH.run_date ,
JH.run_time ,
CAST(CAST(JH.run_date AS VARCHAR) + ' '
+ LEFT(RIGHT('000000' + CAST(JH.run_time AS VARCHAR), 6), 2) + ':'
+ SUBSTRING(RIGHT('000000' + CAST(JH.run_time AS VARCHAR), 6), 3, 2)
+ ':' + RIGHT(RIGHT('000000' + CAST(JH.run_time AS VARCHAR), 6), 2) AS SMALLDATETIME) AS [DateTime] ,

CAST(LEFT(RIGHT('000000' + CAST(JH.run_time AS VARCHAR), 6), 2) + ':'
+ SUBSTRING(RIGHT('000000' + CAST(JH.run_time AS VARCHAR), 6), 3, 2)
+ ':' + RIGHT(RIGHT('000000' + CAST(JH.run_time AS VARCHAR), 6), 2) AS TIME) AS [Time]
FROM msdb.dbo.sysjobhistory AS JH

A little late with a reply maybe help someone else.

Or could also use the undocumented function here http://www.mssqltips.com/sqlservertip/2850/querying-sql-server-agent-job-history-data/
Post #1566961
Posted Friday, May 2, 2014 7:05 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:57 PM
Points: 23,009, Visits: 31,508
Instead of all the conversions you could use the scalar function msdb.dbo.agent_datetime.

For example:


declare @rundate int, @runtime int;

set @rundate = 20140502;
set @runtime = 512;

select msdb.dbo.agent_datetime(@rundate,@runtime);





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1567017
Posted Saturday, May 17, 2014 1:40 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 310, Visits: 537
Hey thanks for the help.
Post #1572033
Posted Sunday, May 18, 2014 11:46 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 36,749, Visits: 31,197
Lynn Pettis (5/2/2014)
Instead of all the conversions you could use the scalar function msdb.dbo.agent_datetime.

For example:


declare @rundate int, @runtime int;

set @rundate = 20140502;
set @runtime = 512;

select msdb.dbo.agent_datetime(@rundate,@runtime);




While that's convenient, if you ever need performance out of it, I don't believe you'll get it. Here's the code from that function. It's hard for me to believe they do conversions to NCHAR in this numeric-only process...
  
CREATE FUNCTION agent_datetime(@date int, @time int)
RETURNS DATETIME
AS
BEGIN
RETURN
(
CONVERT(DATETIME,
CONVERT(NVARCHAR(4),@date / 10000) + N'-' +
CONVERT(NVARCHAR(2),(@date % 10000)/100) + N'-' +
CONVERT(NVARCHAR(2),@date % 100) + N' ' +
CONVERT(NVARCHAR(2),@time / 10000) + N':' +
CONVERT(NVARCHAR(2),(@time % 10000)/100) + N':' +
CONVERT(NVARCHAR(2),@time % 100),
120)
)
END



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1572098
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse