Geek Answers Handbook

Date range between two dates in a LINQ query

I am trying to write a select query that returns records where the range of input dates is between two date fields in a LINQ query.

My inputs:

  • date1 - start date
  • date2 - end date

My database fields

  • Appointmentstart
  • Appointmentend

In addition, I would also like to make sure that input from 14:00 to 15:00 does not return a value for 15: 00-16: 00.

return (from t1 in db.Appointments where (t1.AppointmentStart <= date2 && (t1.AppointmentEnd) >= date1)

If anyone can help me, I will be grateful.

+4
source share
2 answers

He is looking at me in the opposite direction.

if the following is true:

Date1 = start

Date2 = end

then I would think that I start after or equal to the destination start and enddate before or equal to the destination or:

 return (from t1 in db.Appointments where (date1 >= t1.AppointmentStart && date2 <= t1.AppointmentEnd))

I also changed the parens because they didn't make sense to me (it seemed like it was missing)

+1
source

I am not 100% clarifying your requirements. In your start line, you requested records "where the range of input dates is between the two date fields", but in the "Advanced" line, you mean that you do not want to return records where the start date of the meeting is not equal to the end date of the input. I believe these are two different requirements, so I will give you two different requests.

First request:

  from t1 in db.Appointments where date1 >= t1.AppointmentStart where date2 <= t1.AppointmentEnd select t1;

Second request:

  from t1 in db.Appointments where date2 > t1.AppointmentStart where date1 < t1.AppointmentEnd select t1;

The first query returns records that “contain” the input dates.

The second query returns records that "overlap" the input date.

I think it makes sense that you want the overlap request, and this one will match your "14:00 - 15:00", does not return a value for the request 15: 00-16: 00 ".

Let me know if I made a mistake understanding your requirements and you need to make any changes.

+3
source

More articles:


All Articles