Querying SharePoint Calendar for Recurring Events using CAML Query

Calendar items in SharePoint fall into several categories. Single events are those which don’t repeat and only appear once on the calendar, while recurring events may show up any number of times depending on the recurrence pattern selected by a user.

 

Recurring event pattern is set using the options presented when the ‘Make this a repeating event’ checkbox is selected.

 

Querying SharePoint Calendar list is a bit tricky. Let me run through the following query scenarios:

• Querying for all the events that has the start or end date matching current date.
• Querying for all the events that happens in the same week of a given date.
• Querying for all the events that happens in the same month of a given date.
• Querying for all the events that happens in the same year of a given date.

I created a repeating event starting from Jan 1, 2009 with no end date.

Querying for all the events that has the start or end date matching current date :

 

Code :

using (SPSite oSPSite = new SPSite("http://myserver/sites/Events"))

            {

                using (SPWeb oSPWeb = oSPSite.OpenWeb())

                {

                    tSPList = oSPWeb.Lists["EventsCalendar"];

                    CalDate = new DateTime(2009,2,1);

                    oQuery.ExpandRecurrence = true;

                    oQuery.Query = "<Where><DateRangesOverlap>;

<FieldRef Name='EventDate' /><FieldRef Name='EndDate' />

<FieldRef Name='RecurrenceID' /><Value Type='DateTime'><Today />

</Value></DateRangesOverlap></Where>"

                    oQuery.CalendarDate = CalDate;

                    tSPListItems = tSPList.GetItems(oQuery);

                }

            }

In the above snippet note that the value is sent as “Today” and the date set as Calendar date is Feb 1, 2009

 

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

 

This will result in fetching all the events that has the start or end date coinciding with today’s date.

The result bound to the grid will look like this

 

It shows all the events happening on Feb 1, 2009

 

Querying for all the events that happens in the same week of a given date :

 

Code :

using (SPSite oSPSite = new SPSite("http://myserver/sites/Events"))

{

    using (SPWeb oSPWeb = oSPSite.OpenWeb())

    {

        tSPList = oSPWeb.Lists["EventsCalendar"];

        CalDate = new DateTime(2009,2,1);

        oQuery.ExpandRecurrence = true;

        oQuery.Query = "<Where><DateRangesOverlap><FieldRef Name='EventDate' />;

        <FieldRef Name='EndDate' /><FieldRef Name='RecurrenceID' />

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

        </DateRangesOverlap></Where>"

        oQuery.CalendarDate = CalDate;

        tSPListItems = tSPList.GetItems(oQuery);

    }

}

In the above snippet note that the value is sent as “Week” and the date set as Calendar date is Feb 1, 2009

 

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

This will result in fetching all the events that has the start or end date coinciding with that week The result bound to the grid will look like this

It shows all the events starting from Feb 1, 2009 till Feb 8,2009 (Sunday to Sunday) . It is actually picking all the days from the current week and also one extra day from the next week !. The result remains the same for input dates starting Feb 1 through Feb 7.

 

Querying for all the events that happens in the same month of a given date :

 

Code :

using (SPSite oSPSite = new SPSite("http://myserver/sites/Events"))

{

    using (SPWeb oSPWeb = oSPSite.OpenWeb())

    {

        tSPList = oSPWeb.Lists["EventsCalendar"];

        CalDate = new DateTime(2009,2,1);

        oQuery.ExpandRecurrence = true;

        oQuery.Query = "<Where><DateRangesOverlap><FieldRef Name='EventDate' />;

        <FieldRef Name='EndDate' /><FieldRef Name='RecurrenceID' />

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

        </DateRangesOverlap></Where>"

        oQuery.CalendarDate = CalDate;

        tSPListItems = tSPList.GetItems(oQuery);

    }

}

In the above snippet note that the value is sent as “Month” and the date set as Calendar date is Feb 1, 2009

 

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

 

The results that are fetched with this query is a little confusing !!!

 

It gets the events that occurred in the last 7 days from the Jan, 2009 and the entire month from Feb , 2009 and last five days from March , 2009.

Querying for all the events that happens in the same year of a given date :

 

Code :

using (SPSite oSPSite = new SPSite("http://myserver/sites/Events"))

{

    using (SPWeb oSPWeb = oSPSite.OpenWeb())

    {

        tSPList = oSPWeb.Lists["EventsCalendar"];

        CalDate = new DateTime(2009,2,1);

        oQuery.ExpandRecurrence = true;

        oQuery.Query = "<Where><DateRangesOverlap><FieldRef Name='EventDate' />;

        <FieldRef Name='EndDate' /><FieldRef Name='RecurrenceID' />

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

        </DateRangesOverlap></Where>"

        oQuery.CalendarDate = CalDate;

        tSPListItems = tSPList.GetItems(oQuery);

    }

}

In the above snippet note that the value is sent as “Year” and the date set as Calendar date is Feb 1, 2009

 

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

 

The results that are fetched with this query continues to baffle me !!!

 

It gets all the events that occurs starting from today Feb 20, 2009 all the way through Feb 19, 2011.

 

 

I have no clue why the Month and Year filters did not work as someone would expect…