asked on

T SQL cast and convert problem

Hi
I want to convert time in hh.mm.ss in run_time and run_duration and change the run_date to mm/dd/yyyy. Can some one please help?

CREATE TABLE tJobReport
(
lngID INTEGER IDENTITY(1,1)
,server VARCHAR(20)
,jobname VARCHAR(50)
,status VARCHAR(10)
,rundate VARCHAR(10)
,runtime CHAR(8)
,runduration CHAR(8)
)
GO

--step2:

CREATE CLUSTERED INDEX tJobReport_clustered
ON tJobReport(server,jobname,rundate,runtime)
GO

--STEP3:

IF OBJECT_ID('spJobReport') IS NOT NULL
DROP PROCEDURE spJobReport
GO

CREATE PROCEDURE spJobReport
AS
SET NOCOUNT ON

--Server 1
INSERT INTO tJobReport (server, jobname, status, rundate, runtime, runduration)
SELECT sj.originating_server, sj.name,

--What is it in English

CASE sjh.run_status
      WHEN 0 THEN 'Failed'
      WHEN 1 THEN 'Succeeded'
      WHEN 2 THEN 'Retry'
      WHEN 3 THEN 'Canceled'
      ELSE 'Unknown'
END,

sjh.run_date, sjh.run_time, sjh.run_duration

FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=a0009139-s;User ID=;Password='
).msdb.dbo.sysjobs sj

INNER JOIN OPENDATASOURCE(
'SQLOLEDB',
'Data Source=a0009139-s;User ID=;Password='
).msdb.dbo.sysjobhistory sjh
ON sj.job_id = sjh.job_id

--Join for new history rows
left JOIN msdb.dbo.tJobReport jr
ON sj.originating_server = jr.server
AND sj.name = jr.jobname
AND SUBSTRING(CAST(sjh.run_date AS CHAR(8)),5,2) + '/' + RIGHT(CAST(sjh.run_date AS CHAR(8)),2) + '/' + LEFT(CAST(sjh.run_date AS CHAR(8)),4) = jr.rundate
AND LEFT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2) + ':' + SUBSTRING(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),3,2) + ':' + RIGHT(RIGHT('000000' + CAST(run_time AS VARCHAR(10)),6),2) = jr.runtime

--Only enabled jobs
WHERE sj.enabled = 1
--Only job outcome not each step outcome
AND sjh.step_id = 0
--Only completed jobs
AND sjh.run_status <> 4
--Only new data
AND jr.lngID IS NULL

--Latest date first
ORDER BY sjh.run_date DESC

Aneesh

atwork2003,
> I want to convert time in hh.mm.ss in run_time and run_duration

SELECT DATEDIFF(ss, RunTime, RunDuration ) --- will return the run time in secs

atwork2003

ASKER

My question is inreference to the code I have. can you please tell me what are the changes I need to make in the following code to display the runtime and run duration in the hh.mm.ss format and rundate column in mm/dd/yyyy

Thanks

Anthony Perkins

Try it this way:

CONVERT(datetime, CAST(run_date as char(8)), 112) run_date,
STUFF(STUFF(RIGHT('000000' + CAST(run_time as varchar(6)), 6), 3, 0, '.'), 6, 0, '.') run_time

Anthony Perkins

Let's try that again:

CONVERT(char(10), CONVERT(datetime, CAST(sjh.run_date as char(8)), 112), 101) run_date,
STUFF(STUFF(RIGHT('000000' + CAST(sjh.run_time as varchar(6)), 6), 3, 0, '.'), 6, 0, '.') run_time,

folderol

select
run_date,
cast(substring(run_start,1,2) +':'+ substring(run_start,3,2) +':'+ substring(run_start,5,2) as datetime) as run_time,
cast(substring(run_len,1,2) +':'+ substring(run_len,3,2) +':'+ substring(run_len,5,2) as datetime) as run_duration,
date_created, name
from

(
select
cast(right('000000' + cast(sjh.run_date as varchar(8)),8) as datetime) as run_date,
right('000000' + cast(sjh.run_time as varchar(6)),6) as run_start,
right('000000' + cast(sjh.run_duration as varchar(6)),6) as run_len,

sj.date_created, sj.name
from msdb.dbo.sysjobhistory sjh
join msdb.dbo.sysjobs sj on sjh.job_id = sj.job_id
WHERE sj.enabled = 1
--Only job outcome not each step outcome
AND sjh.step_id = 0
--Only completed jobs
AND sjh.run_status <> 4
)
as jobhist

I didn't modify your query, although I included elements. This is just a demonstration of how the columns are packed integers. The decimal places of the int values represent positional notation of the time values. It's not so weird as is sounds, used to be seen in a lot of code.

I nested two selects so I could do the substrings more efficiently. You can nest the substrings into one assignment within on select, but it gives an awkardly long line.

Tom

folderol

acperkins post gave me the idea for this mod, makes more sense than my earlier attempt...

select
run_date + cast(substring(run_start,1,2) +':'+ substring(run_start,3,2) +':'+ substring(run_start,5,2) as datetime) as run_datetime,
cast(substring(run_len,1,2) +':'+ substring(run_len,3,2) +':'+ substring(run_len,5,2) as datetime) as run_duration,
datediff(second,0,cast(substring(run_len,1,2) +':'+ substring(run_len,3,2) +':'+ substring(run_len,5,2) as datetime))as elaspsed_seconds,
date_created, name
from

(
select
cast(right('000000' + cast(sjh.run_date as varchar(8)),8) as datetime) as run_date,
right('000000' + cast(sjh.run_time as varchar(6)),6) as run_start,
right('000000' + cast(sjh.run_duration as varchar(6)),6) as run_len,

sj.date_created,
sj.name
from msdb.dbo.sysjobhistory sjh
join msdb.dbo.sysjobs sj on sjh.job_id = sj.job_id
WHERE sj.enabled = 1
--Only job outcome not each step outcome
AND sjh.step_id = 0
--Only completed jobs
AND sjh.run_status <> 4
)
as jobhist

atwork2003

ASKER

sorry for my ignorance but please show me the modifications in original store proc.

Thanks

Anthony Perkins

Try this:

CREATE PROCEDURE spJobReport

AS

SET NOCOUNT ON

--Server 1
INSERT      tJobReport (server, jobname, status, rundate, runtime, runduration)
SELECT      sj.originating_server,
      sj.name,
      --What is it in English
      CASE sjh.run_status
       WHEN 0 THEN 'Failed'
       WHEN 1 THEN 'Succeeded'
       WHEN 2 THEN 'Retry'
       WHEN 3 THEN 'Canceled'
       ELSE 'Unknown'
      END,
      CONVERT(char(10), CONVERT(datetime, CAST(sjh.run_date as char(8)), 112), 101) run_date,
      STUFF(STUFF(RIGHT('000000' + CAST(sjh.run_time as varchar(6)), 6), 3, 0, '.'), 6, 0, '.') run_time,
      sjh.run_duration
FROM      OPENDATASOURCE(
       'SQLOLEDB',
       'Data Source=a0009139-s;User ID=;Password='
       ).msdb.dbo.sysjobs sj

      INNER JOIN OPENDATASOURCE(
       'SQLOLEDB',
       'Data Source=a0009139-s;User ID=;Password='
       ).msdb.dbo.sysjobhistory sjh ON sj.job_id = sjh.job_id

      --Join for new history rows
      Left JOIN msdb.dbo.tJobReport jr ON sj.originating_server = jr.server
            AND sj.name = jr.jobname
            AND CONVERT(char(10), CONVERT(datetime, CAST(sjh.run_date as char(8)), 112), 101) = jr.rundate
            AND STUFF(STUFF(RIGHT('000000' + CAST(run_time as varchar(6)), 6), 3, 0, '.'), 6, 0, '.') = jr.runtime

      --Only enabled jobs
WHERE      sj.enabled = 1
      --Only job outcome not each step outcome
      AND sjh.step_id = 0
      --Only completed jobs
      AND sjh.run_status <> 4
      --Only new data
      AND jr.lngID IS NULL
--Latest date first
ORDER BY sjh.run_date DESC

atwork2003

ASKER

Can you please also help in changing the runduration column too. It is still showing wrong formate I want hh.mm.ss.

Thanks for all your help

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial