Home Forums SQL Server 2005 Development Producing dates from SYSJOBS and SYSJOBHISTORY RE: Producing dates from SYSJOBS and SYSJOBHISTORY

  • Okay, I am having trouble understanding the why of your reference code as well, so here is what I did:

    SELECT run_time

    , run_time/10000 AS run_time_hours

    , (run_time%10000)/100 AS run_time_minutes

    , (run_time%10000)%100 AS run_time_seconds

    , (run_time/10000 /*run_time_hours*/ * 60 * 60 /* hours to minutes to seconds*/)

    + ((run_time%10000)/100 /* run_time_minutes */ * 60 /* minutes to seconds */ )

    + (run_time%10000)%100 AS run_time_elapsed_seconds

    , CONVERT(DATETIME, RTRIM(run_date)) AS Start_Date

    , CONVERT(DATETIME, RTRIM(run_date)) +

    ((run_time/10000 * 3600) + ((run_time%10000)/100*60) + (run_time%10000)%100 /*run_time_elapsed_seconds*/) / (23.999999*3600 /* seconds in a day*/) AS Start_DateTime

    , ((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100 /*run_duration_elapsed_seconds*/)

    , CONVERT(DATETIME, RTRIM(run_date)) + ((run_time/10000 * 3600) + ((run_time%10000)/100*60) + (run_time%10000)%100) / (86399.9964 /* Start Date Time */)

    + ((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100 /*run_duration_elapsed_seconds*/) / (86399.9964 /* seconds in a day*/) AS End_DateTime

    FROM msdb.dbo.sysjobhistory

    Hopefully this shows my work.

    FYI, the 23.999999 is required to get to the correct precision of seconds.

    After doing this, I am getting a factor of 2.777E-4 versus a factor of 2.893E-4 in your current code. This seems to me to be a difference of rounding. The one thing worth noting is that my solution involves taking the modulus of a modulus, versus the other solution which does not.

    While I know this doesn't answer your question, I hope this helps get to an efficient solution!