QUERY: GET HISTORY of JOBS on a specific DATE

  • Hello,

    Can anyone help me on the following query?

    I need to get the history of jobs on a specific date.

    I've got the following query but it return an error on the output because of date convertion :o|

    SELECT

    j.name as JobName,

    NextRunDate =

    DATENAME(dw, CONVERT(CHAR(8), next_run_date, 112))

    + ', '

    + DATENAME(m, CONVERT(CHAR(8), next_run_date, 112))

    + ' '

    + CONVERT(VARCHAR(12), DAY(CONVERT(CHAR(8), next_run_date, 112)))

    + ', '

    + CONVERT(CHAR(4), YEAR(CONVERT(CHAR(8), next_run_date, 112))),

    + STUFF(STUFF(RIGHT('000000'

    + CONVERT(VARCHAR(8), next_run_time), 6), 5, 0, ':'), 3, 0, ':')

    FROM

    msdb..sysjobs j,

    msdb..sysjobschedules js

    WHERE

    j.job_id = js.job_id

    Server: Msg 241, Level 16, State 1, Line 1

    Syntax error converting datetime from character string.

    Thanks and regards,

    JMSM 😉

  • Try this:

    SELECT j.name AS JobName,

    SUBSTRING(CONVERT(CHAR(8),next_run_date),7,2) + '-'+

    SUBSTRING(CONVERT(CHAR(8),next_run_date),5,2) + '-' +

    SUBSTRING(CONVERT(CHAR(8),next_run_date),1,4)+ ' ' +

    SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6),next_run_time),6),1,2) + ':'+

    SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6),next_run_time),6),3,2) + ':' +

    SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6),next_run_time),6),5,2)

    AS NextRun

    FROM

    msdb..sysjobs j JOIN

    msdb..sysjobschedules js

    ON j.job_id = js.job_id

    [font="Verdana"]Markus Bohse[/font]

  • Thanks for the answer,

    But can you(anyone) tell me one query to get job history of all jobs that were running on a specific date (November 09(nine))

    Thanks and regards,

    JMSM 😉

  • This script might not include exactly the columns you're looking for but it should help to start you up. Ideally replace GETDATE() at the end with some variables and use it as a stored procedure. Also of course instead of GETDATE() it's better to round it to a full day.

    Use msdb

    GO

    SELECT j.name as Jobname,

    j.Description,

    l.name as Owner,

    s.srvname as Server,

    [Status] = CASE

    WHEN run_status = 1 THEN 'Succeeded'

    WHEN run_status = 2 THEN 'Retry'

    WHEN run_status = 3 THEN 'Canceled'

    WHEN run_status = 4 THEN 'In progress'

    WHEN run_status = 0 THEN 'Failed'

    ELSE 'Status not available'

    END,

    CASE len(h.run_duration)

    WHEN 1 THEN cast('00:00:0'

    + cast(h.run_duration as char) as char (8))

    WHEN 2 THEN cast('00:00:'

    + cast(h.run_duration as char) as char (8))

    WHEN 3 THEN cast('00:0'

    + Left(right(h.run_duration,3),1)

    +':' + right(h.run_duration,2) as char (8))

    WHEN 4 THEN cast('00:'

    + Left(right(h.run_duration,4),2)

    +':' + right(h.run_duration,2) as char (8))

    WHEN 5 THEN cast('0'

    + Left(right(h.run_duration,5),1)

    +':' + Left(right(h.run_duration,4),2)

    +':' + right(h.run_duration,2) as char (8))

    WHEN 6 THEN cast(Left(right(h.run_duration,6),2)

    +':' + Left(right(h.run_duration,4),2)

    +':' + right(h.run_duration,2) as char (8))

    END as 'Duration'

    FROM sysjobs j

    LEFT JOIN sysoperators o

    ON j.notify_email_operator_id = o.id

    JOIN master..syslogins l

    ON j.Owner_sid = l.sid

    JOIN sysjobservers s1

    ON j.job_id = s1.job_id

    JOIN master..sysservers s

    ON s1.server_id = s.srvid

    LEFT JOIN sysjobschedules sch

    ON j.job_id = sch.Job_id

    LEFT JOIN dbo.sysjobhistory h

    ON j.job_id = h.Job_id

    WHERE SUBSTRING(CONVERT(char(8),h.Run_date),5,2) + '-'+

    SUBSTRING(CONVERT(char(8),h.Run_date),7,2) + '-' +

    SUBSTRING(CONVERT(char(8),h.Run_date),1,4)+ ' ' +

    SUBSTRING(RIGHT('000000' + CONVERT(varchar(6),h.run_time),6),1,2) + ':'+

    SUBSTRING(RIGHT('000000' + CONVERT(varchar(6),h.run_time),6),3,2) + ':' +

    SUBSTRING(RIGHT('000000' + CONVERT(varchar(6),h.run_time),6),5,2)BETWEEN GETDATE() and DATEADD(dd,-1,Getdate())

    [font="Verdana"]Markus Bohse[/font]

  • [font="Courier New"]SELECT Job.[name], Hst.[sql_message_id], Hst.[message] , Hst.[run_date], Hst.[run_time], run_duration

    FROM [msdb].dbo.sysjobhistory Hst

    INNER JOIN [msdb].dbo.sysjobs Job ON Hst.[job_id] = Job.[job_id]

    where convert(varchar(8), GETDATE()-1,112)= Hst.[run_date]

    and name not like '%replication%' and name not like '%distribution%' -- omit all the replication jobs

    ORDER BY /*Job.[name],*/Hst.[run_date] DESC, Hst.[run_time] DESC[/font]

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

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