sharepoint DateRangesOverlap - find overlaping items for a date range

Question

I am looking for a sample syntax to identify what items overlap with my calendar entry.

We have a Calendar list in Sharepoint. I would like to build a custom query for identifying overlaping items for the "new" calendar entry.

It looks like DateRangesOverlap is what I would need , but its not clear for me , how to set it up with the date range . For example :

My new meeting is from monday to wednesday this week. sharepoint , please give me all the items that overlap with "monday to Wednesday this week"

thanks, Gabor

Answer 1

DateRangesOverlap is not what you want, despite the name.

Its used behind the scenes to support the calendar views when an instance of a recurring event would fall in the Day/Week or Month views so it only works with and in the CAML.

(for extra confusion note that when using Month its not the calendar month but what would show on a calendar view of that month - so for July 2010 it would be 28th June to 1st August.)

Answer 2

I've just published a solution for this problem. I hope it answers your question.

The simplest way and the best description in the topic is the one I found on this question. 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:

protected String BuildDateRangeOverlapFilter(DateTime startDate, DateTime endDate)
{
    StringBuilder sb = new StringBuilder();
    sb.Append(String.Format("<Where><And>{0}{1}</And></Where>",
        BuildSimpleDateFilter("StartDate", endDate, "Leq"),
        BuildSimpleDateFilter("DueDate", startDate, "Geq")));

    return sb.ToString();
}


protected String BuildSimpleDateFilter(String dateFieldName, DateTime filterDate, String relation)
{
    String filter = String.Format("<{0}><FieldRef Name='{1}'/><Value Type='DateTime'>{2}</Value></{0}>",
        relation, dateFieldName, SPUtility.CreateISO8601DateTimeFromSystemDateTime(filterDate));

    return filter;
}

And here is an example about the usage:

DateTime startDate = DateTime.Today.AddDays(11);
DateTime endDate = DateTime.Today.AddDays(15);

SPList taskList = web.Lists["Tasks"];

SPQuery query = new SPQuery();
query.ViewFields = "<FieldRef Name='Title'/><FieldRef Name='StartDate'/><FieldRef Name='DueDate'/>";
query.Query = BuildDateRangeOverlapFilter(startDate, endDate);

SPListItemCollection matches = taskList.GetItems(query);

foreach (SPListItem match in matches)
{
    Console.WriteLine(match["Title"]);
}

A few notes about usage:

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.
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!
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.