Sign In

OUR NEWS

Looking for some insight or inspiration?

At Provoke we are fortunate to have some of the world's leading experts in Microsoft technology. Whether you want to know the latest in SharePoint and Office 365, understand the changing world of Unified Communications, or gain insight into the Cloud and Windows Azure, this is where to get it. Our News is the place to find the latest events, blogs and articles from the clever monkeys at Provoke and our partners.

DateRangesOverlap CAML Query Element
Jan 23, 2014

By Preetesh Chauhan, Technical Specialist, Provoke Solutions Wellington

​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

{

    Query =

    @"<Where>

        <DateRangesOverlap>

            <FieldRef Name='EventDate' />

            <FieldRef Name='EndDate' />

            <FieldRef Name='RecurrenceID' />

            <Value Type='DateTime'><Now/></Value>

        </DateRangesOverlap>

    </Where>

    <OrderBy>

        <FieldRef Name='EventDate' Ascending='True' />

    </OrderBy>",

    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.

 ARTICLES