Home Forums SQL Server 2012 SQL 2012 - General Adding YYYYMMDD and HHMMSS to get mm/dd/yyyy HH:MM:SS RE: Adding YYYYMMDD and HHMMSS to get mm/dd/yyyy HH:MM:SS

  • June 12, 2014 at 3:52 pm

    #1721266

    There's no need to make that extra work with the date. You need to work with the run_duration column because it comes in format HHMMSS as well.

    selectsjh.Job_ID,

    run_date,

    CONVERT(varchar(20), CONVERT(date, CONVERT(varchar(8), sjh.run_date), 112),110) run_date,

    CONVERT(date, CONVERT(varchar(8), sjh.run_date), 112) run_date2,

    DATEADD ( -- Add execution seconds to the start time after converting YYYYMMDD HHMMSS to MM/DD/YYYY HH:MM:SS format

    ss,

    ((sjh.Run_Duration / 100) * 60) + (sjh.Run_Duration % 100),

    CAST( run_date AS char(8)) + ' '

    + STUFF( STUFF( RIGHT( '00000' + CAST( sjh.run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':')

    ) AS NewRunTime,

    sjh.run_duration,

    sjh.run_status

    frommsdb..sysjobhistory sjh -- ON j.ExternalJobID = sjh.Job_id

    wheresjh.run_date = CONVERT (VARCHAR (8), GetDate(), 112)

    and sjh.step_id = 0

    and run_status in (0, 1)

    Note that I don't expect jobs that run for one hour or more.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2