I am currently querying the msdb.dbo.sysjobhistory
table to find the status of the jobs executed today and show them in a browser. The definition of this table looks like this.
Here run_date
and run_time
correspond to the date and time when a particular job was executed both represented as int
of course. I am trying to construct a DateTime
type from these attributes so that it will be easier to view it in a single column rather than two separate columns.
Example
run_date = 20161125
run_time = 33000
runStartTime = 2016-11-25 03:30:00
The viewmodel class I have defined is
public class ExecutedJobsViewModel
{
public string jobName { get; set; }
public int stepID { get; set; }
public string stepName { get; set; }
public string message { get; set; }
public DateTime runStartTime { get; set; }
public int runStatus { get; set; }
public int runDuration { get; set; }
}
and the code which fetches this data is
private async Task<List<ExecutedJobsViewModel>> getExecutedJobs()
{
using(var context = new SysTableEntity())
{
return await (from job in context.sysjobs
join history in context.sysjobhistories
on job.job_id equals history.job_id
select new ExecutedJobsViewModel
{
jobName = job.name,
message = history.message,
stepID = history.step_id,
stepName = history.step_name,
runStatus = history.run_status,
runDuration = history.run_duration,
runStartTime = // Bind data here
}).ToListAsync();
}
}
The only solution I could think of was DbFunctions.CreateDateTime(int? years, int? months, int? days, int? hours, int? minutes, int? seconds)
which is too much work given that the int
has to be converted to string
and then find the sub-string and then convert back to int
. Also, handle edge case like 33000
which are five digits as opposed to time which is represented as six digits number (183000
).
Can you suggest an easier way to achieve this.
Your first option would be to use dbo.agent_datetime
in your query to get a datetime value from SQL (refer to this example)
If you can't do that, then you can use DateTime.ParseExact
to specify the format the datetime is in:
var date = DateTime.ParseExact("20161125", "yyyyMMdd",
System.Globalization.CultureInfo.InvariantCulture);
var time = DateTime.ParseExact("183000", "HHmmss",
System.Globalization.CultureInfo.InvariantCulture)
This will not handle the case where the time is 5 digits only. I am not sure how to interpret that, but if the edge case is always representing a missing hour, you can add that using PadLeft
, for example
var time = "33000";
if(time.Length == 5) time = time.PadLeft(6, '0');
If the missing digit is always a second, then you'd want to use PadLeft