CODEWITHSUNDEEP
c#entity-frameworkdatetime
Stevie White
Stevie White

Reputation: 579

How to compare just month and day from a datetime object

In my C# program I have run into an obstacle where I have a table that stores date ranges (columns are date range ID (int), begin date (DateTime) and end date (DateTime). I want to query the table and get back rows that only fall within a specific date range. I cannot use datetime.date since that includes the year.

So for example, I want to query the table and get all date ranges that fall between 01-01 and 5-31.

I have tried using the following lambda to query the table, but my result set is empty.

List<DateRanges> tempDateRangeList = dataContext
                                     .DateRanges
                                     .Where(r=>r.BeginDate.Month <= startDate.Month 
                                               && r.EndDate.Month >= finishDate.Month)
                                     .ToList();

tempDateRangeList = tempDateRangeList.Where(r=>r.BeginDate.Day <= startDate.Day 
                                               && r.EndDate.Day >= finishDate.Day)
                                     .ToList();

Does anyone have any suggestions on how I could accomplish this?

Edit:

Examples of BeginDate and EndDate would be a list such as follows:

BeginDate 1/1/2016, 5/25/2016, 9/11/2016

EndDates 5/24/2016, 9/10/2016, 12/31/2016

Filter date would be: startDate = 12/8 finishDate = 12/12

Expected result: Begin Date of 9/11 End date of 12/31

Upvotes: 9

Views: 9604

Answers (2)

Andrea Grillo
Andrea Grillo

Reputation: 125

If a lambda expression isn't compulsory, I've used linq queries (because it was the first solution i've thought).

var validRanges = from range in ranges
                  where range.BeginDate.CompareTo(startDate) <= 0
                  where range.EndDate.CompareTo(endDate) >= 0
                  select range;

Using CompareTo is the easiest way to compare two DateTime struct. I invite you to take a look here for a complete description of the method.

Edit

If you aren't interested in hours of your dates, but only in Day and Month, you should use range.BeginDate.Date.CompareTo(startDate.Date) and range.EndDate.Date.CompareTo(endDate.Date)

Upvotes: 0

Zoran Horvat
Zoran Horvat

Reputation: 11321

There are two cases in your condition - month equal to boundary month, in which case you must test day number, and a different month, in which you ignore day. Hence the query:

List<DateRanges> tempDateRangeList = 
    dataContext.DateRanges.Where(r =>
        ((r.BeginDate.Month < startDate.Month) || 
         (r.BeginDate.Month == startDate.Month && r.BeginDate.Day <= startDate.Day)) &&
        ((r.EndDate.Month > finishDate.Month) ||
         (r.EndDate.Month == finishDate.Month) && r.EndDate.Day >= finsihDate.Day))
    .ToList();

Condition is ugly and very hard to follow but covers all cases. This query returns all records which define date ranges that completely fall under the boundary dates.

If you wish to find records that are just overlapping (completely or partially) with the filtering range, then the query would be:

List<DateRanges> tempDateRangeList = 
    dataContext.DateRanges.Where(r =>
        ((r.BeginDate.Month < endDate.Month) || 
         (r.BeginDate.Month == endDate.Month && r.BeginDate.Day <= endDate.Day)) &&
        ((r.EndDate.Month > startDate.Month) ||
         (r.EndDate.Month == startDate.Month) && r.EndDate.Day >= startDate.Day))
    .ToList();

This condition may bend your mind, but it works fine.

Upvotes: 3

Related Questions