Second Life of a Hungarian SharePoint Geek

November 11, 2011

How to find overlapping items for a date range

Filed under: CAML, SP 2010 — Tags: , — Peter Holpar @ 13:33

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")));
  8.     return sb.ToString();
  9. }
  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));
  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);
  4. SPList taskList = web.Lists["Tasks"];
  6. SPQuery query = new SPQuery();
  7. query.ViewFields = "<FieldRef Name='Title'/><FieldRef Name='StartDate'/><FieldRef Name='DueDate'/>";
  8. query.Query = BuildDateRangeOverlapFilter(startDate, endDate);
  10. SPListItemCollection matches = taskList.GetItems(query);
  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.


1 Comment »

  1. I need to read all recurring and non recurring events from sharepoint calendar, any working reference would be appreciated. I tried daterangeoverlapping and expandrecrnce elements in caml but behavior is very crazy, not getting all except around one year.

    Comment by Inthiyaz — May 21, 2015 @ 08:00

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at

%d bloggers like this: