Convert Job Duration Format

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  July 26, 2012 at 7:37 pm

  #264388

  The following script returns the run_duration as for example:

  45028

  43520

  3007

  40529

  2509

  I want 45028 to be formatted as 4:50:28 hh:mm:ss.

  USE MSDB

  GO

  SELECT j.name AS JobName,h.step_name AS StepName,

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

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

  h.run_duration AS 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 AS h

  INNER JOIN sysjobs AS j

  ON j.job_id = h.job_id

  WHERE j.Name = 'AS400_Download'

  AND h.step_name = '(Job outcome)'

  AND h.run_duration > '1500'

  ORDER BY j.name, h.run_date DESC , h.run_duration DESC--h.run_time DESC

  Any help would be greatly appreciated.

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 27, 2012 at 3:34 am

  #1518367

  You do know that you had everything there you needed to do this, right?

  here is my code, with your WHERE clause commented:

  SELECT

  j.name AS JobName,

  h.step_name AS StepName,

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

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

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

  --h.run_duration AS 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 AS h

  INNER JOIN sysjobs AS j

  ON j.job_id = h.job_id

  --WHERE

  -- j.Name = 'AS400_Download'

  -- AND h.step_name = '(Job outcome)'

  -- AND h.run_duration > '1500'

  ORDER BY

  j.name,

  h.run_date DESC,

  h.run_duration DESC--h.run_time DESC