2

I'm querying SharePoint for calendar events given a date. This works for events other than all day events. Event dates in SharePoint are stored as UTC except for all day events which are stored as the local site timezone. How would I include all day events in the CAML query? This is my CAML query which works for events for a date. It checks for EventDate and EndDate for a date and for events that have started but haven't ended.

<View>
  <Query>
    <Where>
      <Or>
        <And>
          <Geq>
            <FieldRef Name='EventDate' />
            <Value StorageTZ='FALSE' IncludeTimeValue="FALSE" Type="DateTime">2018-07-03T00:00:00-0700</Value>
          </Geq>
          <Leq>
            <FieldRef Name='EndDate' />
            <Value StorageTZ='FALSE' IncludeTimeValue="FALSE" Type="DateTime">2018-07-03T23:59:59-0700</Value>
          </Leq>
        </And>
        <And>
          <Leq>
            <FieldRef Name='EventDate' />
            <Value StorageTZ='FALSE' IncludeTimeValue="FALSE" Type="DateTime">2018-07-03T00:00:00-0700</Value>
          </Leq>
          <Geq>
            <FieldRef Name='EndDate' />
            <Value StorageTZ='FALSE' IncludeTimeValue="FALSE" Type="DateTime">2018-07-03T00:00:00-0700</Value>
          </Geq>
        </And>
      </Or>
    </Where>
  </Query>
</View>

I've looked at this link but couldn't get the DateRangesOverlap in this CAML to work.

<Where>
    <Or>
        <Or>
            <And>
                <Eq>
                    <FieldRef Name='fAllDayEvent' />
                    <Value Type='AllDayEvent'>1</Value>
                </Eq>
                <Geq>
                    <FieldRef Name='EndDate' />
                    <Value Type='DateTime'>
                        <Today />
                    </Value>
                </Geq>
            </And>
            <DateRangesOverlap>
              <FieldRef Name='EventDate' />
              <FieldRef Name='EndDate' />
              <FieldRef Name='RecurrenceID' />
              <Value Type='DateTime' IncludeTimeValue='TRUE'>
                  <Today />
              </Value>
            </DateRangesOverlap>
        </Or>
       <Geq>
          <FieldRef Name='EventDate' />
          <Value Type='DateTime' IncludeTimeValue='TRUE'>
             <Today />
          </Value>
       </Geq>
    </Or>
</Where>

I want to add this to my CAML so the query includes all day events but I'm not sure if it will work.

0

Here is a small example that will request any Sharepoint Calendar event ( doesnt matter if it is an "all day event" or not:

<Where>
  <Or>
  <And>
      <Eq>
        <FieldRef Name="EventDate" />
        <Value IncludeTimeValue="False" Type="DateTime">
          <Today />
        </Value>
      </Eq>
      <Eq>
        <FieldRef Name="fAllDayEvent" />
        <Value Type="Text">False</Value>
      </Eq>
    </And>
    <And>
      <Eq>
        <FieldRef Name="EventDate"/>
        <Value IncludeTimeValue="False" Type="DateTime">
            <Today OffsetDays='-1' />
        </Value>
      </Eq>
      <Eq>
        <FieldRef Name='fAllDayEvent' />
        <Value Type="Text">True</Value>
      </Eq>
    </And>
  </Or>
</Where>

Take into account that "all day Event" Events are considered by Sharepoint as if they were starting the previous day.

EXAMPLE:

  • Some event is created for the 2nd of July, and saved as an "all day event".
  • We add a new (calculated) column to our SP Calendary library, "Real Start Date" => =DAY([Start Time])
  • Calculated date column will tell 1st of July, instead of 2nd.

If you modify your filtering so it contains dates that start with (targetDate -1) and "allDayEvent" to true, you will reach the expected results ;)

On the other hand, depeding on which time zone is defined for your site, you may need to change the value.

More info here

  • Remember that not all time zones are UTC -hrs. Some are UTC+ so if the local time zone is e.g. Australia then this won't work. Also for times that = UTC (Like the UK in winter) – Ryan Sep 13 '18 at 20:08
  • Yes thats true. However, what I mean with the answer is that the "all day" events are interpreted in a funny way. I had also issues retrieveing the event in certain period of time ( including the "all day" event ones ), and I have to do multiple filters and play with the "IncludeTimeValue=Yes/No" value – Alberto Sep 14 '18 at 10:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.