please correct the query time convertion

Question

'Last_Time'=
STUFF(LEFT(CAST(REPLICATE('0',6-LEN(LAST_TIME))+CAST(LAST_TIME AS VARCHAR(20))
AS VARCHAR(20)),4),3,0,':')

the out put was Last_time & Next_time and  coming as       

10003

how to change it to time for exmple : 9:00 AM  

the main query is below.

SELECT
REPLACE(substring(a.name,1,60),'''','') as Failed_jobs,b.last_run_date as
Last_date,b.last_run_time as Last_time,c.next_run_date as Next_Date,
c.next_run_time as Next_time FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B,
msdb.dbo.sysjobschedules C " & ChrW(9) &" WHERE A.job_id =
B.job_id and A.job_id = C.job_Id  AND B.last_run_outcome = 0 and A.enabled
<> 0 AND  RIGHT(CAST(A.JOB_ID AS VARCHAR(50)),17) NOT IN
(SELECT      
STUFF(REVERSE(SUBSTRING(REVERSE(RTRIM(program_name)),11,16)),5,0,'-') AS
Job        FROM master.dbo.sysprocesses
sp   JOIN msdb.dbo.sysjobs sj      ON
STUFF(REVERSE(SUBSTRING(REVERSE(RTRIM(sp.program_name)),11,16)),5,0,'-') =
RIGHT(sj.job_id, 17) WHERE program_name LIKE 'SQLAgent - TSQL JobStep %')"

Answer 1

Could you tell what is the value for LAST_TIME.

---

Please mark the post as answered if it answers your question

Answer 2

Please explain what are you trying to achieve?

Please post sample data to test it.

DECLARE @i int
DECLARE @d Datetime
SET @i= 10003

SET @d = DATEADD(ss,@i,'19000101')

SELECT CONVERT(VARCHAR, @d, 114)

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and cleansing

Answer 3

Hi,

Whats the datatype and value for LAST_TIME?

Regards
Satheesh

Answer 4

This is the main query

SELECT
REPLACE(substring(a.name,1,60),'''','') as Failed_jobs,b.last_run_date as
Last_date,b.last_run_time as Last_time,c.next_run_date as Next_Date,
c.next_run_time as Next_time FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B,
msdb.dbo.sysjobschedules C " & ChrW(9) & " WHERE A.job_id =
B.job_id and A.job_id = C.job_Id  AND B.last_run_outcome = 0 and A.enabled
<> 0 AND  RIGHT(CAST(A.JOB_ID AS VARCHAR(50)),17) NOT IN
(SELECT       
STUFF(REVERSE(SUBSTRING(REVERSE(RTRIM(program_name)),11,16)),5,0,'-') AS
Job        FROM master.dbo.sysprocesses
sp   JOIN msdb.dbo.sysjobs sj      ON
STUFF(REVERSE(SUBSTRING(REVERSE(RTRIM(sp.program_name)),11,16)),5,0,'-') =
RIGHT(sj.job_id, 17) WHERE program_name LIKE 'SQLAgent - TSQL JobStep %')"

Answer 5

Any progress?

The following part should be converted to clean T-SQL(it looks like from the app):

msdb.dbo.sysjobschedules C " & ChrW(9) & " WHERE A.job_id =

---

Kalman Toth SQL SERVER 2012 & BI TRAINING