How to find overlapping items for a date range

It is a common requirement to find list items (like tasks) that have an overlapping duration with a specified data range.

Several years ago I wrote a post about the DateRangesOverlap CAML element and about its strange behavior returning items outside of the specified month range. As I suggested there, we can (and should) create the right query using basic CAML elements.

It is easy to create a complex CAML query to filter items for overlapping date ranges, however, there is a way to create a simple query as well. The simplest way and the best description in the topic is the one I found on stackoverflow. It says, the date ranges are overlapping when it is true for both of them, that their start date is no later than the end date of the other date range. That is very clear if you think about that just a bit.

Based on that concept I’ve created two helper methods:

1. protected String BuildDateRangeOverlapFilter(DateTime startDate, DateTime endDate)
2. {
3.     StringBuilder sb = new StringBuilder();
4.     sb.Append(String.Format("<Where><And>{0}{1}</And></Where>",
5.         BuildSimpleDateFilter("StartDate", endDate, "Leq"),
6.         BuildSimpleDateFilter("DueDate", startDate, "Geq")));
7.  
8.     return sb.ToString();
9. }
10.  
11.  
12. protected String BuildSimpleDateFilter(String dateFieldName, DateTime filterDate, String relation)
13. {
14.     String filter = String.Format("<{0}><FieldRef Name='{1}'/><Value Type='DateTime'>{2}</Value></{0}>",
15.         relation, dateFieldName, SPUtility.CreateISO8601DateTimeFromSystemDateTime(filterDate));
16.  
17.     return filter;
18. }

And here is an example about the usage:

1. DateTime startDate = DateTime.Today.AddDays(11);
2. DateTime endDate = DateTime.Today.AddDays(15);
3.  
4. SPList taskList = web.Lists["Tasks"];
5.  
6. SPQuery query = new SPQuery();
7. query.ViewFields = "<FieldRef Name='Title'/><FieldRef Name='StartDate'/><FieldRef Name='DueDate'/>";
8. query.Query = BuildDateRangeOverlapFilter(startDate, endDate);
9.  
10. SPListItemCollection matches = taskList.GetItems(query);
11.  
12. foreach (SPListItem match in matches)
13. {
14.     Console.WriteLine(match["Title"]);
15. }

Note 1: The start date of the date ranges must be less than or equal to the end date, but I think that is an acceptable restriction.

Note 2: Before you ask it, I’ve not tested this solution with recurring events, but I assume it does not work in that case. Sorry!

Note 3: To convert my date stored as DateTime to String I used earlier the DateTime.ToString(String format) method with format pattern "yyyy-MM-ddT00:00:00Z", later I found that more-or-less matches the UniversalSortableDateTimePattern, so switched to parameter “u”. If you check the source code of the SPUtility.CreateISO8601DateTimeFromSystemDateTime method used in this sample (for example with Reflector), you can see that instead of simply calling DateTime.ToString with the right pattern, it takes the individual parts of the DateTime (like year, month, day, etc.), and creates the result by appending these component to each other using a StringBuilder. At first it was quite strange for me, but then thought, it must be the fastest way of conversion, as it does not have the overhead of pattern recognition and lot of conditions found in DateTime.ToString. However, if you should work with time zone information, then it might be not ideal for you. Although ISO 8601 supports time zones, AFAIS it is not implemented in CreateISO8601DateTimeFromSystemDateTime, for example, by providing an overload with a DateTimeOffset parameter. I don’t know if CAML supports at all this kind of time zone information provided in the date string. I admit it is not a crucial question most of the cases, however it might produce a surprise if not taken into account. You can read a bit more about that here.

Advertisement

Tags: CAML, SP 2010