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.

image

 

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

 

image

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

image

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

image

image

image

 

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

image

image

 

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…

Advertisements

6 Responses

  1. I have the same problem trying to get all events for current year. New Year (1/1/2009) won’t show up in list if I use “Year”, and is OK if I use “Month”.

  2. What is the syntax for including an in the above Where clauses??

  3. It should read an AND clause

  4. Actually it completely ignores the Date property when you select . It just starts at todays date, as my test shows when adjusting the date in vmware enviroment

  5. […] a client side application also slightly with a different functionality. I took help from this blog on how to query a SharePoint Calendar list using CAML query but still it really drove me […]

  6. Hi Karthik,

    Glad to see your post. Clean explanation about DaterangesOverlap.

    +1 for this.

    i have a question, everything working fine for me as you said, except the below,

    I didnt get the past events starting from today.

    I have mentioned the start date as below

    CalDate = new DateTime(2009,2,1)

    But didnt worked out… My calendar starts from todays date.

    Any solution ??/

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

%d bloggers like this: