Comparing dates in linq

Question

I would like to query the db for items with a date greater than or equal to a given date.

The date in the db is a datetime and records time.

If the user enters the search string "1/30/2014", they expect entries that occurred at any time on that date to be returned. However, anything that has a time after 12 am is not returned.

I know I could simply add a day to the search string, but is there a more appropriate way?

if (form["closedend"] != "")
{
     DateTime d = DateTime.Parse(form["closedend"]);
     traces = traces.Where(s => s.date_Closed >= d);
}

score 14 · Accepted Answer · edited May 23 '17 at 12:11

14

You can use the Date property to truncate the time part:

 traces = traces.Where(s => s.date_Closed.Date <= d.Date);

On this way you'd include this day since both DateTimes are midnight.

Update if you use LINQ to Entities DateTime.Date is not supported, you could use this solution: Using DateTime in LINQ to Entities

.Where(s => EntityFunctions.TruncateTime(s.date_Closed) <=  EntityFunctions.TruncateTime(d))

edited May 23 '17 at 12:11

Community

1
1

answered Oct 08 '14 at 14:18

Tim Schmelter

450,073
74
686
939

1

I'm not sure if the OP is using Linq-to-Entities (they mention a db), but if they are `Date` will not work. – juharr Oct 08 '14 at 14:21
4

I ended up using DbFunctions as EntityFunctions is deprecated. Thank you for your help! – tintyethan Oct 08 '14 at 16:29
Warning CS0618 `EntityFunctions` is obsolete: 'This class has been replaced by System.Data.Entity.DbFunctions.' DbFunctions.TruncateTime(AnDateTime)` – Sayed Mahmoud Jun 01 '22 at 23:46

score 0 · Answer 2 · answered Oct 08 '14 at 14:33

0

You said

with a date greater than or equal to a given date

but in your code you write

s.date_Closed <= d

I think you must change <= by >= to obtain dates greater or equal to d, now you're getting dates less or equal to d.

answered Oct 08 '14 at 14:33

iag

166
4

score 0 · Answer 3 · answered Oct 08 '14 at 17:53

For this example you don't need neccessary any other dll. You can implement other function, which return bool, f.e:

public bool MyFunction(DateTime s)
{
   DateTime d = DateTime.Parse(Your constructor);
   return (s.date_Closed >= d);
}

var query = from obj in traces
            where MyFunction(obj.date_Closed.Date)
            select obj;

or:

var query = traces.Where(p => MyFunction(p.data_Closed.Date));

score 0 · Answer 4 · answered Jun 01 '22 at 23:49

You can use DbFunctions.TruncateTime(StartDateTime) To remove the time from datetime.

    if (form["closedend"] != "")
    {
         DateTime d = DateTime.Parse(form["closedend"]);
         traces = traces.Where(s => DbFunctions.TruncateTime(s.date_Closed) >= DbFunctions.TruncateTime(d));
    }

Comparing dates in linq

4 Answers4