CAML query for Sharepoint Calender Lists

Question

I am using CAML query for SharePoint calender list. I am able fetch proper daily data. but when i choose weekly, monthly and yearly, it doesn't give me reports as expected. it doesn't take date specified rather it takes start date of present week.

how can i solve this problem?

qryBookings.Query = 
"<Where>
    <And>
        <DateRangesOverlap>
            <FieldRef Name='EventDate'/>
            <FieldRef Name='EndDate'/>
            <FieldRef Name='RecurrenceID'/>
            <Value Type='DateTime'>
                <Week/>
            </Value>
        </DateRangesOverlap>
        <And>
            <Eq>
                <FieldRef Name='Place'/>
                <Value Type='Lookup'>" + d.SelectedItem.Text + "</Value>
            </Eq>
            <Eq>
                <FieldRef Name='Test'/>
                <Value Type='Lookup'>" + R.SelectedItem.Text + "</Value>
            </Eq>
        </And>
    </And>
</Where>";

qryBookings.CalendarDate = DateTime.Parse(txtDate.Text);
qryBookings.ExpandRecurrence = true;

Answer 1

The weekly approach starts with the first day of current week. I guess there is nothing you can do about this, except always to work on Sunday ;-)

I guess the point is to make a function of your own to compute desire starting and ending date. Then apply the correct "BETWEEN" statement in CAML. With GREATER and LESSER since there is no shortcut.

Hope that helps

Answer 2

Not sure why this is not working, but an alternative would be to apply the filtering directly in the CAML Query.

First step would be to decompose the DateTime.Now into its year, month and day parts. Based on that you can generate a Start Date and an End Date and pass those in the CAML, filtering by StartDate (EventDate) and EndDate.

Answer 3

You can use a usefull tool called CAML Builder, really helping :) : http://www.u2u.be/Software

Answer 4

In CAML query you can use <BeginsWith> tag to solve your problem. You can also use <Leq> or <Geq>.

Refer these below link which may helps you.

• https://sharepoint.stackexchange.com/questions/136098/caml-query-data-between-two-date-sets
• https://social.msdn.microsoft.com/Forums/sharepoint/en-US/55d939cf-f96c-4c2f-94cf-1d82a44129e1/caml-query-where-date-between

Answer 5

I am using the old soap service (Lists.asmx, GetListItems) to retrieve calender items for the current month. Used this webservice to be able to retrieve recurrences. My query is very similar to yours except that I am supplying the start date in a different way.

Query:

          <Where>
           <And>
              <Geq><FieldRef Name="EndDate"/><Value Type="DateTime">" + monthStartDate + "</Value></Geq>
            <DateRangesOverlap>
              <FieldRef Name="EventDate" />
              <FieldRef Name="EndDate" />
              <FieldRef Name="RecurrenceID" />
              <Value Type='DateTime'><Month/></Value>
            </DateRangesOverlap>
           </And>
        </Where>

Queryoptions:

        <queryOptions>
            <QueryOptions>
              <ExpandRecurrence>TRUE</ExpandRecurrence>
              <DateInUtc>TRUE</DateInUtc>
             <CalendarDate>" + monthStartDate + "</CalendarDate>
            </QueryOptions>
        </queryOptions>