locked
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

    or

    12345 -> 01:23:45


    Thanks in advance


    http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx


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

Answers

All replies

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

    https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/dbo-sysjobhistory-transact-sql?view=sql-server-ver15

    (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',
           run_date,
           run_time,
           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) +''
    else
    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