A while ago I had trouble writing a CAML query that retrieved reoccurring events for a particular time period. At the time I found there was a lack of resource on the web that explained how to properly use the DateRangesOverlap element in the CAML query.
Here I will demonstrate how to write a CAML query using the DateRangesOverlap element to retrieve events and recurring events. Let's say we have an event with a daily recurrence that generates 10 separate events:
If you were to look for that event through code you will likely only see one event.
Using the DateRangesOverlap element of the CAML query we can retrieve past and future instances of the recurring event:
var eventsQuery = new SPQuery
<FieldRef Name='EventDate' />
<FieldRef Name='EndDate' />
<FieldRef Name='RecurrenceID' />
<FieldRef Name='EventDate' Ascending='True' />
ViewFields = "<FieldRef Name='Title' /><FieldRef Name='EventDate' /><FieldRef Name='EndDate' /><FieldRef Name='Location' />",
ExpandRecurrence = true,
CalendarDate = DateTime.Now
From the above code you will notice that DateRangeValue is set to Now. What this does is fetches any recurring events where the start date (EventDate) and the end date (EndDate) overlap the specified value.
Other value types are:
Today – Returns events for the current day
Week – Retrieves events across the week
Month – Gets all events for the current month
Year – Retrieves events that occur in the year
Setting ExpandRecurrence to true expands recurrent events in your calendar view. It is important to set ExpandRecurrence to true in the SPQuery if you want recurring items returned.
Finally, CalendarDate is the date the query uses to get events for. For example, if CalendarDate is set to DateTime.Now.AddDays(-7) and the value type is Week, the query will retrieve events that occurred in the week that the date falls on. So if today's date is 28 November 2013, the query will retrieve events between 18 November 2013 and 25 November 2013.