CODEWITHSUNDEEP
c#sql-serverasp.net-mvclinqdatetime
Kgn-web
Kgn-web

Reputation: 7555

How to remove time portion from Nullable<DateTime> in LINQ

Below is my LINQ

 IQueryable<ReportMapper> query;

                    query = (from c in entities.tDocumentStatus
                             join d in entities.tTOCStructures on c.DocumentId equals d.DocumentID
                             join e in entities.tUsers on d.LastUpdatedBy equals e.UserUID
                             orderby d.CreatedOn descending
                             where
                              (docMode <= 0 || docModes.Contains(c.StatusId)) &&
                              d.FolderType == 2 && d.isDeleted == false && d.ClientID == clientId &&
                              (EntityFunctions.TruncateTime(d.CreatedOn) >= startDate.Date && EntityFunctions.TruncateTime(d.CreatedOn) <= endDate.Date) 
                             select new ReportMapper()
                             {
                                 DocumentName = d.DocumentName,
                                 AssignedDate = c.AssignedDate==null? (EntityFunctions.TruncateTime(d.LastUpdatedOn)):  (EntityFunctions.TruncateTime(c.AssignedDate)),
                                 ReviewStatus = c.tStatu.StatusName,
                                 ActionPerformedBy = e.FirstName + " " + e.LastName
                             });

I need to export this data to excel. I am in need to remove the complete time portion from Assigned Date.

When I am using EntityFunctions.TruncateTime it truncates the time to 00:00:00

But my need is to remove this portion.

I tried the following:-

Export to Excel

   public void ExportToExcel()
    {
        ExcelPackage excel = new ExcelPackage();
            var worksheet = excel.Workbook.Worksheets.Add("Sheet1");
       fileName = "Document_Status_Report_" + DateTime.Now.ToString("yyyyMMdHHmmss") + ".xlsx";
                worksheet.Cells[1, 1].LoadFromCollection(data, true);
 using (MemoryStream swObj = new MemoryStream())
            {
                response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                response.AddHeader("Content-Disposition", "attachment; filename=" + fileName + "");
                excel.SaveAs(swObj);
                swObj.WriteTo(response.OutputStream);
                return;
            }

    }

Upvotes: 2

Views: 5967

Answers (3)

Rishabh Jain
Rishabh Jain

Reputation: 179

Best works for me was:-

manufactured_dates = detail.manufactured_date == null ? "" : ((DateTime)detail.manufactured_date).ToShortDateString()

Where I needed to get date into a string format out of a Nullable<DateTime> in LINQ, so as it was Nullable<DateTime> I did 

(DateTime)detail.manufactured_date

Then to extract just the date as a string out of Converted DateTime Value I applied Value.ToShortDateString()

ie: ((DateTime)detail.manufactured_date).ToShortDateString()

Or you can also do 

((DateTime)detail.expiry_date).ToString("dd.MMM.yyyy")

Upvotes: 0

Gustav
Gustav

Reputation: 55906

Use .Value.Date as you already have tried. Never strings.

Your issue is not the time part (@PanagiotisKanavos explained that) but the format applied to the cells in Excel. Just adjust this to display the date part only.

Upvotes: 1

jignesh
jignesh

Reputation: 1669

DateTime always has a time and you can not truncate or remove time from Datetime.If you want only date then suggest to add new string property in ReportMapper class and assign only Date string.As in below code I have assigned date in 'MM/dd/yyyy' format to AssignedDateString.

IQueryable<ReportMapper> query;

                    query = (from c in entities.tDocumentStatus
                             join d in entities.tTOCStructures on c.DocumentId equals d.DocumentID
                             join e in entities.tUsers on d.LastUpdatedBy equals e.UserUID
                             orderby d.CreatedOn descending
                             where
                              (docMode <= 0 || docModes.Contains(c.StatusId)) &&
                              d.FolderType == 2 && d.isDeleted == false && d.ClientID == clientId &&
                              (EntityFunctions.TruncateTime(d.CreatedOn) >= startDate.Date && EntityFunctions.TruncateTime(d.CreatedOn) <= endDate.Date) 
                             select new ReportMapper()
                             {
                                 DocumentName = d.DocumentName,
                                 AssignedDate = c.AssignedDate==null? (EntityFunctions.TruncateTime(d.LastUpdatedOn)):  (EntityFunctions.TruncateTime(c.AssignedDate)),
                                 ReviewStatus = c.tStatu.StatusName,
                                 ActionPerformedBy = e.FirstName + " " + e.LastName
                                 AssignedDateString= c.AssignedDate==null? (d.LastUpdatedOn.ToString("MM/dd/yyyy")):  (c.AssignedDate.ToString("MM/dd/yyyy")),
                             });

Upvotes: 0

Related Questions