Andrew Day Andrew Day - 4 months ago 16
SQL Question

Linq to produce custom period in days, hours, minutes and seconds

var resultsFromSql = from wb in reportContext.WorkItems
where wi.DateRequired >= model.FirstDate && wi.DateRequired <= model.SecondDate
select new
{
wi.Reference,
dateRequired = SqlFunctions.DatePart("yyyy", wi.DateRequired) + "/" + SqlFunctions.DatePart("mm", wi.DateRequired) + "/" + SqlFunctions.DatePart("dd", wi.DateRequired),
earliestActionDate = SqlFunctions.DatePart("yyyy", wi.EarliestActionDate) + "/" + SqlFunctions.DatePart("mm", wi.EarliestActionDate) + "/" + SqlFunctions.DatePart("dd", wi.EarliestActionDate),
mainRef = wi.MainReferenceId != 0 ? wi.MainReferenceId.ToString() : " ",
subRef = wi.SecondaryReference ?? "",
details = wi.DisplayDetails ?? "",
createdOn = SqlFunctions.DatePart("yyyy", wi.CreatedOn) + "/" + SqlFunctions.DatePart("mm", wi.CreatedOn) + "/" + SqlFunctions.DatePart("dd", wi.CreatedOn),*/ /*wi.UpdatedBy,
updatedOn = SqlFunctions.DatePart("yyyy", wi.UpdatedOn) + "/" + SqlFunctions.DatePart("mm", wi.UpdatedOn) + "/" + SqlFunctions.DatePart("dd", wi.UpdatedOn),
timeTaken = SqlFunctions.DateDiff("day", wi.UpdatedOn, wi.CreatedOn) + " days "
/*+ SqlFunctions.DatePart("h", wi.UpdatedOn - wi.CreatedOn) + ":"
+ SqlFunctions.DatePart("n", wi.UpdatedOn - wi.CreatedOn) + ":"
+ SqlFunctions.DatePart("s", wi.UpdatedOn - wi.CreatedOn)*/,
timeStuff = SqlFunctions.DatePart("h", wi.UpdatedOn /*- wi.CreatedOn*/)
};


TimeTaken and TimeStuff wont work due to DatePart not supporting hours etc, and I get other errors relating to the dateTime maths of the commented portion of timeTaken. How can I create a timeTaken value of x days, hours, minutes and seconds which is the length of time calculated from updatedOn minus createdOn which are dateTimes.

/*List<T> list = new List<T>();*/
foreach (var res in resultsFromSql.ToList())
{
res.timeTaken = res.CreatedOn; /*= SqlFunctions.DateDiff("day", res.UpdatedOn, res.CreatedOn)#1#
}


If I run a foreach afterwards I'm unable to assign values to any of the properties as the anonymous types are readonly. How can I get my days(full days value, not remaining part of month i.e. 197days) with the leftover hours(max 24), minutes(max 60) and seconds(max 60).

Answer
public string ConvertToDayHourMinSec(DateTime firstTime, DateTime secondTime)
    {
        var timeTaken = new TimeSpan(firstTime.Ticks - secondTime.Ticks);
        var days = timeTaken.ToString("%d"); 
        var hms = timeTaken.ToString(@"hh\:mm\:ss");
        return days + " days, " + hms;
    }

Thanks to @Kevin's answer which lead me down the right path. The code is a bit heavy for inline use so I created the above function which can be called with any dateTime i.e.

UpdatedMinusCreated = ConvertToDayHourMinSec(res.UpdatedOn, res.CreatedOn),

and gives the format 4 days, 03:07:42