Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

T SQL cast and convert problem

Avatar of atwork2003
atwork2003 asked on
Microsoft SQL Server
10 Comments1 Solution2085 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Commented:
This problem has been solved!
Unlock 1 Answer and 10 Comments.
See Answers