Include All day events in CAML query for a date

Question

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.

Answer 1

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