Extra Recurring Calendar Events Returned By Caml Query For Single Day

Question

My C# application runs outside SharePoint, so I'm using CSOM with a Caml query. I want to find all calendar events, single and recurring, that are scheduled for a single day. I'm getting ALL recurring calendar events that overlap my selected day even if they do not have any scheduled events on the day in question. Single events are correctly filtered and returned. How do I filter the recurring events to only those that fire on the given date? Is this any easier with a Task list rather than a Calendar list?

var today = DateTime.UtcNow.ToString(@"yyyy-MM-ddT12:00:00Z");

var query = new CamlQuery {
   ViewXml = @"<View>              
     <ViewFields>
        <FieldRef Name='ID' />
        <FieldRef Name='Title' />
        <FieldRef Name='EventDate' />
        <FieldRef Name='EndDate' />
        <FieldRef Name='Description' />
        <FieldRef Name='Category' />
        <FieldRef Name='fRecurrence' />
        <FieldRef Name='RecurrenceData' />
        <FieldRef Name='fAllDayEvent' />
     </ViewFields> 
     <Query>
        <Where>
           <DateRangesOverlap>
              <FieldRef Name='EventDate'></FieldRef>
              <FieldRef Name='EndDate'></FieldRef>
              <FieldRef Name='RecurrenceID'></FieldRef>
              <Value Type='DateTime'><Today/></Value>
           </DateRangesOverlap>
        </Where>
        <OrderBy><FieldRef Name='EventDate' /></OrderBy> 
     </Query>
     <QueryOptions>
        <CalendarDate>"+ today + @"</CalendarDate>
        <RecurrencePatternXMLVersion>v3</RecurrencePatternXMLVersion>
        <ExpandRecurrence>TRUE</ExpandRecurrence>
     </QueryOptions>
     </View>"
   };

That's a typical school exercise. I don't use CAML, but AFAIK there is no native way to do that. My bet would be to get the overlapping tasks, then calculate/check if it fires at the current/selected date. — Kilazur, May 19 '14 at 07:44
Well, it's not for school, it's not an exercise, and I'm afraid you're correct about the calculate/check process. It just doesn't seem to be documented anywhere. I'm still digging for an answer. — JimBoone, May 26 '14 at 21:15

pholpar · Answer 1 · 2017-01-20T10:31:39.750

It is possible using the DateRangesOverlap CAML operator and the Lists web service (no support in the Client OM or in OData / REST!) from the client side, see description here.

You can define a variable for the current date as:

var today = DateTime.UtcNow.Date;
var todayAsString = today.ToString("yyyy-MM-dd");

Then you COULD use its value in your query to restrict the result for the events scheduled for today:

string.Format("<Query><Where><And><Eq><FieldRef Name='EventDate' /><Value Type='DateTime' IncludeTimeValue='FALSE'>{0}</Value></Eq><Eq><DateRangesOverlap><FieldRef Name='EventDate' /><FieldRef Name='EndDate' /><FieldRef Name='RecurrenceID' /><Value Type='DateTime'><Month/></Value></DateRangesOverlap></And></Where><Query>", todayAsString);

But based on my experience, this kind of query won't work as you might expect. If you include another DateTime-based condition in the query beyond DateRangesOverlap, the recurring events won't be expanded again. Just another 'kind' surprise from SharePoint. The expanding of recurring events seems to work only if you use DateRangesOverlap, and you use only this kind of DateTime-based condition in your query (but no other condition like <Eq> or <Gt> combined with a DateTime field).

You CAN however combine DateRangesOverlap with other kind of conditions (like <Eq> or <Gt>), as long as they are related to other field types, like Number or Text.

It means you should query for all event in the current Month (as in the sample CAML query above), for example by setting the CalendarDate QueryOption, then you should add a loop with an extra condition in your code (I mean C# or JavaScript, not in CAML!) to compare the event dates with the current one. Really cumbersome.

Extra Recurring Calendar Events Returned By Caml Query For Single Day

1 Answers1