How to convert int value to time format ? RRS feed

  • Question

  • Hi

    i've quering some data from msdb.dbo.sysjobhistory view, but some value such as [run_time] column is int value.

    I'm looking for a way to convert it to time format. for example :

    222830 -> 22:28:30


    12345 -> 01:23:45

    Thanks in advance


    • Edited by Hamed_1983 Sunday, June 21, 2020 8:41 PM
    Sunday, June 21, 2020 8:41 PM


All replies

  • The Help provides the needed query, e.g.


    (BTW, I was thinking about something like this but then decided to do a quick google search first).

    For every expert, there is an equal and opposite expert. - Becker's Law

    My blog

    My TechNet articles

    • Marked as answer by Hamed_1983 Sunday, June 21, 2020 8:55 PM
    Sunday, June 21, 2020 8:47 PM
  •  SELECT j.NAME AS 'Job',
           msdb.dbo.Agent_datetime(run_date, run_time) [Run_Datetime] 
    ,Case when run_duration>24*10000 then
    Cast(run_duration%240000/10000 as varchar(3)) +' (day) '
    + Cast(run_duration/240000 as varchar(2))  
    +':' + LefT(Right(Cast(run_duration as varchar(8)),4),2)
    +':'+ Right(Cast(run_duration as varchar(8)),2) +''
    Format(msdb.dbo.agent_datetime(19000101,  run_duration) ,'HH:mm:ss')
    end [Duriation in HH:MM:SS]
    FROM   msdb.dbo.sysjobs j
           INNER JOIN msdb.dbo.sysjobhistory h
                   ON j.job_id = h.job_id
    WHERE  j.enabled = 1
    ORDER  BY run_duration DESC

    Sunday, June 21, 2020 10:16 PM