Sql agent job history for less than 24 hours

Question

Post reply

Sql agent job history for less than 24 hours

hydbadrose

SSCarpal Tunnel

Points: 4164
More actions
November 23, 2010 at 7:28 pm

#243203

USE msdb

Go

I want to get the SQL job agent history for less than 24 hours or for the current day. Can someone please help me to complete the script becuase the getdate() is giving the arithimetic error. please help.

SELECT j.name JobName,h.step_name StepName,

CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate,

STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime,

h.run_duration StepDuration,

case h.run_status when 0 then 'failed'

when 1 then 'Succeded'

when 2 then 'Retry'

when 3 then 'Cancelled'

when 4 then 'In Progress'

end as ExecutionStatus,

h.message MessageGenerated

FROM sysjobhistory h inner join sysjobs j

ON j.job_id = h.job_id

ORDER BY j.name, h.run_date, h.run_time

GO

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Anam Verma SSCertifiable Points: 5365 More actions · Answer 1

How about this:

SELECT j.name JobName,h.step_name StepName,



CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate,

STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime,

h.run_duration StepDuration,

case h.run_status when 0 then 'failed'

when 1 then 'Succeded'

when 2 then 'Retry'

when 3 then 'Cancelled'

when 4 then 'In Progress'

end as ExecutionStatus,

h.message MessageGenerated

FROM sysjobhistory h inner join sysjobs j

ON j.job_id = h.job_id

where run_date >= CONVERT(varchar(10),getdate(),112)