Equal date is not displayed in date range search

Question

When I set the end date, for example, 31.08.2018, does not find records with August 31 date inclusive, but they exist in DB, only until August 30th inclusive. I need a date display with the 31st day of the month.

Date in model

//...
public Nullable<System.DateTime> dt_corr { get; set; }
//...

Controller

public ActionResult Index(DateTime? startdate, DateTime? enddate, int? page)
{
   var samp = from s in db.Samples
              select s;

   if (startdate != null)  // also tried startdate.HasValue 
   {
      samp = samp.Where(s => s.dt_corr >= startdate); //also tried startdate.Value 
      ViewBag.StartDate  = startdate;
   }
   if (enddate != null)// also tried enddate.HasValue
   {
      samp = samp.Where(s => s.dt_corr <= enddate); // also tried enddate.Value , no difference
      ViewBag.EndDate = enddate;  
      {
         int pageSize = 10;
         int pageNumber = (page ?? 1);
         return View(sampl.ToPagedList(pageNumber, pageSize));
      }

View

    //...
    @using (Html.BeginForm("Index", "Samples", FormMethod.Get))
    {
       <p>
          Date
          @Html.Label("StartDate", "Start Date:")
          <input class="startdate" id="startdate" name="startdate" type="date" value="">
          @Html.Label("EndDate", "Final Date:")
          <input class="enddate" id="enddate" name="enddate" type="date" value="">  // in the example class="startdate" too, no difference
          <input type="submit" value="Search"/>
      </p>
    }
    // ...
    Page @(Model.PageCount < Model.PageNumber ? 0 : Model.PageNumber) of 
    @Model.PageCount

    @Html.PagedListPager(Model, page => Url.Action("Index",
        new { page, startdate = ViewBag.StartDate, enddate = ViewBag.EndDate }))

Does `dt_corr` hold a date value, or a datetime value? (The name doesn't give much of a clue what it actually is - consider renaming that variable.) — Richardissimo, Dec 19 '18 at 07:14

score 1 · Answer 1 · answered Dec 19 '18 at 07:02

1

It seems that in your database there will be a record with dt_corr = "2018-08-31 12:33:32.130" and what you're comparing with is enddate = "2018-08-31 00:00:00.000". So those records are not coming.

Try as below.

samp = samp.Where(s => s.dt_corr != null && s.dt_corr.Value.Date <= enddate.Value.Date);

answered Dec 19 '18 at 07:02

Karan

12,059
3
24
40

1

Unfortunately a server error in the application "The specified type "Date" is not supported in LINQ to Entities". But thanks for the explanation how my date query works – Nick Dec 19 '18 at 07:38

MKH · Accepted Answer · 2018-12-19T07:44:45.870

1

We should truncate Time from DateTime when we want to compare just Date,So you can use EntityFuctions.TruncateTime() method like this :

 samp = samp.Where(s => EntityFunctions.TruncateTime(s.dt_corr) <= EntityFunctions.TruncateTime(enddate));

EntityFunctions is placed in System.Data.Objects namespace so add using System.Data.Objects; to your class.

edited Dec 19 '18 at 07:44

answered Dec 19 '18 at 07:14

MKH

367
1
4
12

glad it helped . – MKH Dec 19 '18 at 07:44
Thank you, it works, now the records 31st inclusive are displayed. However VS writes - not recommended class 'EntityFunctions' is obsolete: This class has been replaced by System.Data.Entity.DbFunctions.' – Nick Dec 19 '18 at 07:47
@Nick As you said that `VS writes - not recommended.` I hope this link will be helpful https://stackoverflow.com/a/33802779/9695286 – Karan Dec 19 '18 at 07:54
1

@Nick yes its replaced with `System.Data.Entity.DbFunctions` in Entity framework version 6.0 – MKH Dec 19 '18 at 08:04
1

@Karan and thanks again. Just replaced 'EntityFunctions.TruncateTime()' for the 'DbFunctions.TruncateTime()' and no remarks from the VS – Nick Dec 19 '18 at 08:58

score 0 · Answer 3 · answered Dec 19 '18 at 09:01

When you use "aug 31st" as enddate, you are really using "aug 31st, midnight" (0:00). So you miss almost all of that date - as you found.

Simple solution: just add a day and use a < comparison

instead of

samp = samp.Where(s => s.dt_corr <= enddate);

use

var realend = enddate.Value.AddDays(1);
samp = samp.Where(s => s.dt_corr < realend);

Equal date is not displayed in date range search

3 Answers3