August 31, 2016 at 6:59 am
Might be a dumb question, but why is run_date in MSDB sysjobhistory set as data type INT? And why are run_date and run_time in separate columns? Seems odd that they both aren't just in the same column as datetime, or is there a valid reason why it's set as it is?
August 31, 2016 at 8:48 am
I would say that it's because of a bad decision applied to the design. However, that could only be answered by the people who actually did the design or at least, people involved on the coding.
August 31, 2016 at 9:00 am
There are no dumb questions, only dumb decisions. Whoever designed the MSDB job activity tables didn't make the temporal columns as user friendly as they could be.
Here are a couple of excerpts from a query I use for returning job activity. My approach is to convert int to varchar, pad with zeros, and then parse what I need using substring.
substring(js.last_run_duration,1,3)
+ ':' + substring(js.last_run_duration,4,2)
+ ':' + substring(js.last_run_duration,6,2) as step_last_duration
substring( right('000000' + cast(jr.next_run_time as varchar(6)),6) , 1, 2)
+ ':' + substring( right('0000000' + cast(jr.next_run_time as varchar(6)),6) , 3, 2) as next_run_time
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 31, 2016 at 9:22 am
Eric M Russell (8/31/2016)
There are no dumb questions, only dumb decisions. Whoever designed the MSDB job activity tables didn't make the temporal columns as user friendly as they could be.Here are a couple of excerpts from a query I use for returning job activity. My approach is to convert int to varchar, pad with zeros, and then parse what I need using substring.
substring(js.last_run_duration,1,3)
+ ':' + substring(js.last_run_duration,4,2)
+ ':' + substring(js.last_run_duration,6,2) as step_last_duration
substring( right('000000' + cast(jr.next_run_time as varchar(6)),6) , 1, 2)
+ ':' + substring( right('0000000' + cast(jr.next_run_time as varchar(6)),6) , 3, 2) as next_run_time
One little snag... run_duration can include a "days" component - where the run_duration field is >= 1000000, then the "millions" are the number of days.
One little useful thing - there's an undocumented function "msdb.dbo.agent_datetime" that takes run_date and run_time type fields and converts them into a proper datetime field...
Thomas Rushton
blog: https://thelonedba.wordpress.com
September 1, 2016 at 6:55 am
ThomasRushton (8/31/2016)
Eric M Russell (8/31/2016)
There are no dumb questions, only dumb decisions. Whoever designed the MSDB job activity tables didn't make the temporal columns as user friendly as they could be.Here are a couple of excerpts from a query I use for returning job activity. My approach is to convert int to varchar, pad with zeros, and then parse what I need using substring.
substring(js.last_run_duration,1,3)
+ ':' + substring(js.last_run_duration,4,2)
+ ':' + substring(js.last_run_duration,6,2) as step_last_duration
substring( right('000000' + cast(jr.next_run_time as varchar(6)),6) , 1, 2)
+ ':' + substring( right('0000000' + cast(jr.next_run_time as varchar(6)),6) , 3, 2) as next_run_time
One little snag... run_duration can include a "days" component - where the run_duration field is >= 1000000, then the "millions" are the number of days.
One little useful thing - there's an undocumented function "msdb.dbo.agent_datetime" that takes run_date and run_time type fields and converts them into a proper datetime field...
Thanks for the tip about the agent_datetime() function! 🙂
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply