Agent Jobs

Convert Last_Run_Date And Last_Run_TIme in SysJobSteps

How to convert Last_Run_Date

If you found this post… You probably want to make more sense out of the last_run_date, and last_run_time columns from the msdb..sysjobsteps table. Here is a quick conversion for you. I did a basic conversion to formulate a date/time you could use in other functions. It’s the “last run literal”, then I converted again to extrapolate the Day name using the datename function, and then once more to get an abbreviated Month/Day/Year & time.

-- use this to get all step info about your job, and steps, including last run history.
select
 'job name' = sj.name
, 'step id' = sjs.step_id
, 'step name' = sjs.step_name
, 'last run literal' = dateadd(millisecond, sjs.last_run_time,convert(datetime,cast(nullif(sjs.last_run_date,0) as nvarchar(10))))
, 'last run day' = datename(dw, dateadd(millisecond, sjs.last_run_time,convert(datetime,cast(nullif(sjs.last_run_date,0) as nvarchar(10)))))
, 'last run date' = convert(char, dateadd(millisecond, sjs.last_run_time,convert(datetime,cast(nullif(sjs.last_run_date,0) as nvarchar(10)))), 9)
from
 msdb..sysjobs sj join msdb..sysjobsteps sjs on sj.job_id = sjs.job_id
order by
 sj.name, sjs.step_id asc

Advertisements

3 replies »

  1. Yep. Tried it. Works fine. Although for more precision you could work in a different time conversion. You can tell by the date conversion to Day Name (dd), Month and Year; that this was meant for human eyes. Exact precision on the miliseconds is not helpful to the human eye. This is probably for a report of some sort. Only in automation does the precision count, and I don’t think this is what was intended for this one.

    Overall a good piece of SQL.

    Thanks to this guy.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s