Converting Time and Date represented as INT into DateTime structure in LINQ

c# entity-framework-6 linq sql-server

Question

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. sysjobshistory Table Definition

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.

1
1
11/25/2016 6:19:04 PM

Accepted Answer

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

2
11/25/2016 6:38:15 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow