May 1, 2012 at 9:44 am
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?
May 1, 2012 at 10:15 am
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?
May 1, 2012 at 10:19 am
If what time, the integer value represents the time the job or job step started in the format hhmmss.
May 1, 2012 at 10:25 am
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
:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:
May 1, 2012 at 10:29 am
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
???
May 1, 2012 at 11:59 am
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.
May 2, 2014 at 3:01 am
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/
May 2, 2014 at 7:06 am
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);
May 17, 2014 at 1:40 pm
Hey thanks for the help.
May 18, 2014 at 11:46 am
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
Change is inevitable... Change for the better is not.
May 18, 2014 at 1:04 pm
Jeff Moden (5/18/2014)
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
True, it is inefficient. Here is another option:
cast(cast(run_date as varchar(8)) + ' ' + stuff(stuff(right('000000' + cast(run_time as varchar(6)),6),5,0,':'),3,0,':') as datetime)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply