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

remove time from datetime
how to remove time from datetime in c#
how to get date part from datetime in linq
c# remove time from datetime string
how to get time part from datetime in linq
dbfunctions truncatetime datetime now
entity framework core truncate time
entity framework date without time

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:-

  • (EntityFunctions.TruncateTime(d.LastUpdatedOn)).Value.Date
  • (EntityFunctions.TruncateTime(d.LastUpdatedOn).GetValueOrDefault().Date)
  • (EntityFunctions.TruncateTime(d.LastUpdatedOn)).ToString().Date

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;
            }

    }

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.

How to remove time from nullable datetime column LINQ , If i understand you correctly, you want to return only date part (ignore time part). As F-ES Sitecore[^] mentioned in the comment to the question,� 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:-(EntityFunctions.TruncateTime(d.LastUpdatedOn)).Value.Date

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")),
                             });

Remove the time part of a date in C #, The problem is that you are using Nullable DateTime and not DateTime normal. This is to convert DateTime to string , if you really want to remove the time part � TruncateTime(Nullable<DateTime>) When used as part of a LINQ to Entities query, this method invokes the canonical TruncateTime EDM function to return the given date with the time portion cleared.

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")

Not able to remove time portion of date in my linq to sql code, Value because it's a DateTime? (nullable DateTime). For rendering in the View, you may need to do String.Format("{0:d}", Model.StartDate) as . The .Date property is not supported in Linq to Entities (though it may be supported in other implementations of Linq). I realize that you only want to compare the dates, but there is no real problem with comparing the datetimes if the dbas and dbit values are datetimes with time 00:00:00.

EntityFunctions.TruncateTime Method (System.Data.Objects , The date to truncate. Returns. Nullable<DateTime>. The input date with the time portion cleared. Attributes. EdmFunctionAttribute� @Corak aware of that, but since nullable is technically a generic you can still utulize the underlying class's methods. I should have added that you do need to use the Value property though to use the underlying class methods. I'll edit my post to make it more clear. – JNYRanger Sep 24 '13 at 13:04

DbFunctions.TruncateTime Method (System.Data.Entity), When used as part of a LINQ to Entities query, this method invokes the canonical TruncateTime EDM function to return the given date with the time portion cleared. TruncateTime(Nullable<DateTime>). When used as part of a LINQ to Entities� A type is said to be nullable if it can be assigned a value or can be assigned null, which means the type has no value whatsoever. By default, all reference types, such as String, are nullable, but all value types, such as Int32, are not. In C# and Visual Basic, you mark a value type as nullable by using the ? notation

DateTime In C#, C# DateTime class is used to represent and work with Date and time. Managing Nullable DateTime; Parse to DateTime object from String; Working with Gets the DateTime portion of the value without regard to the offset. Nullable reference types. 04/21/2020; 7 minutes to read +6; In this article. C# 8.0 introduces nullable reference types and non-nullable reference types that enable you to make important statements about the properties for reference type variables:

Comments
  • The property is DateTime which always has a 'time' component. Format it to a string if you only want the date portion
  • You can extract the Date part of a DateTime with DateTime.Date. The time part is available through DateTime.TimeSpan.
  • If you have problems with Excel, post the relevant code. Excel has no issues with time parts - it doesn't have date-only dates at all!. Dates in Excel are stored as a decimal offset from 1899-12-30. You can get that value with DateTime.ToOADate. It's a lot easier though to use a library like Epplus and let it do any conversions
  • @PanagiotisKanavos, ExportToExccel() added
  • @Kgn-web that is Epplus. What is the problem then? If you used .Date the cell will contain 00:00:00 in the time part and the stored date value will have no seconds at all (ie no fractional part). The same holds with EntityFunctions.TruncateTime. Did you use a date-only style for the column?
  • I have added my ExportToExcel() can you please tell where do I specify the dateformat
  • You can define a range in the worksheet - the (partial) column holding your date values - then set the Format property of that range.
  • That format will cause problems in 90% of the world. There's no reason to use strings when talking about dates. If you do need to use strings, use an unambiguous format like ISO8601
  • Oh, and that format will definitely fail with Excel. Excel stores dates as a decimal number. MM/dd/yyyy will be treated as text at best, or interpreted as the wrong date
  • @PanagiotisKanavos I agreed with you. In above code it's not necessary to use 'MM/dd/yyyy' only.I have just suggested how to format DateTime in particular date format.