Post reply

INT to hh:mm:ss

  • April 18, 2008 at 2:19 pm

    #74894

    Hello:

    I've developed a small script to find out about job duration in an SQL Server 2000 instance we have.

    I'm not literate about T-SQL at all and would like to know how to convert the int values I get in run_duration to hh:mm:ss

    Please forgive my ignorance!

    Here is the script.

    Regards.

    Martin

    USE msdb

    GO

    CREATE TABLE #tJobs (

    [name] char (50),

    run_duration int)

    INSERT INTO #tJobs

    SELECT j.[name],

    SUM (h.run_duration)

    FROM msdb.dbo.sysjobhistory h

    INNER JOIN msdb.dbo.sysjobs j

    ON h.job_id = j.job_id

    INNER JOIN msdb.dbo.sysjobsteps s

    ON j.job_id = s.job_id

    GROUP BY j.[name]

    Select * FROM #tJOBS

    DROP TABLE #tJobs

  • April 18, 2008 at 3:02 pm

    #804600

    If I remember correctly - the duration is expressed in some funky notation ("integer" version of HHMMSS), so in order to add them together - you'd have to convert them FIRST to a datetime type, then add them up to see the overal duration.

    That would be something like this:

    dateadd(hh,run_duration/10000,dateadd(minute,(run_duration/100)%100,dateadd(second,run_duration%100,0)))

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 2 posts - 1 through 1 (of 1 total)

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