Caml query to get the next event from calendar

Question

I'm facing a problem building a caml query for the calendar list. Here is my caml query

                SPQuery query = new SPQuery();

                StringBuilder sb = new StringBuilder();
                sb.Append("<Where>");
                sb.Append("<And>");
                sb.Append("<DateRangesOverlap>");
                sb.Append("<FieldRef Name='EventDate' /><FieldRef Name='EndDate' /><FieldRef Name='RecurrenceID' />");
                sb.Append("<Value Type='DateTime'><Month /></Value>");
                sb.Append("</DateRangesOverlap>");
               sb.Append("<Geq><FieldRef Name='EventDate'/><Value Type='DateTime'><Today Offset='-1'/></Value></Geq>");
                sb.Append("</And>");
                sb.Append("</Where>");

                sb.Append("<OrderBy><FieldRef Name='EventDate' /></OrderBy>");

                query.RowLimit = numberOfUpcomingEvents;
                query.Query = sb.ToString();
                query.ExpandRecurrence = true;
                query.CalendarDate = DateTime.Now;

The problem is that it won't work properly if there is an recurring event that starts before current date but the next recurrence is on current date after. How to retrieve recurrence events as single events?

Anyone has suggestions?

Answer 1

Try adding in an additional option <QueryOptions><RecurrencePatternXMLVersion>v3</RecurrencePatternXMLVersion><ExpandRecurrence>TRUE</ExpandRecurrence></QueryOptions>

Answer 2

try this

using (SPSite spsite = SPContext.Current.Site)
            {
                using (SPWeb web = spsite.OpenWeb())
                {  
                    SPQuery query = new SPQuery();
                    SPList list = web.Lists["CalendarList"];
                    query. ExpandRecurrence = true;
                   // query.RowLimit = 20;
                    query.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>";
                    query.ViewFields = "<FieldRef Name='Title' LinkToItem='TRUE'/><FieldRef Name='EventDate' /><FieldRef Name='EndDate' /><FieldRef Name='fRecurrence' /><FieldRef Name='RecurrenceData' /><FieldRef Name='Location' />";                         
                    SPListItemCollection listItems;
                    listItems = list.GetItems(query);
                    foreach (SPListItem item in listItems)
                    {
                        if (item["fRecurrence"].ToString() == "True")  
                        {                          
                          Console.WriteLine(items["Titile"].ToString());                   
                          Console.ReadLine();
                        }
                    }
             }
        }

Answer 3

Just got the solution.

I read it on another thread and the summary is:

1. Create two new Columns called Start Filter and End Filter. Make these columns Calculated Columns with formulas of [Start Time] and [End Time] respectively. The columns should be of the Date and Time\Date and Time type and should not be displayed on the Default View.

2. Click Advanced Settings and choose Yes to the question "Allow management of Content Types" in the Content Types section. Click OK.

My query to get uppcoming events with recurring events is:

<Query>
  <OrderBy>
    <FieldRef Name='EventDate' />
  </OrderBy>
  <Where>
    <Or>
      <And>
        <Leq>
          <FieldRef Name='Start_x0020_Filter' />
          <Value Type='DateTime'>
            <Today Offset='-1' />
          </Value>
        </Leq>
        <Geq>
          <FieldRef Name='End_x0020_Filter' />
          <Value Type='DateTime'>
            <Today Offset='-1' />
          </Value>
        </Geq>
      </And>
      <Geq>
        <FieldRef Name='Start_x0020_Filter' />
        <Value Type='DateTime'>
          <Today Offset='-1' />
        </Value>
      </Geq>
    </Or>
  </Where>
</Query>

Now I can filter all upcoming events