CAML Query not working on weeks that have split months

Question

So, I have been using this query in my SharePoint app for Months and its has always worked perfectly fine. But i just realized that if the week I'm trying to get has a split month like July 31st to August 4th, it will only return List Items for July 31st??? I've tried everything I can think of to get this to work and nothing. How do I get it to work? I'm at a loss.

<View>
  <Query>
    <Where>
      <And>
        <Geq>
          <FieldRef Name='EstimatedDelivery'/>
          <Value IncludeTimeValue='TRUE' Type='DateTime'>startDate</Value>
        </Geq>
        <Leq>
          <FieldRef Name='EstimatedDelivery'/>
          <Value IncludeTimeValue='TRUE' Type='DateTime'>endDate</Value>
        </Leq>
      </And>
    </Where>
  </Query>
</View>

Here is the Javascript code

startDate = startDate.toISOString();
endDate = endDate.toISOString();

var camlQuery = new SP.CamlQuery();
var filterString = '<View><Query>';
filterString = filterString + '<Where>';
filterString = filterString + '<And>';
filterString = filterString + '<Geq>';
filterString = filterString + '<FieldRef Name=\'EstimatedDelivery\'/>';
filterString = filterString + '<Value IncludeTimeValue=\'TRUE\' Type = \'DateTime\'>' + startDate + '</Value>';
filterString = filterString + '</Geq>';
filterString = filterString + '<Leq>';
filterString = filterString + '<FieldRef Name=\'EstimatedDelivery\'/>';
filterString = filterString + '<Value IncludeTimeValue=\'TRUE\' Type = \'DateTime\'>' + endDate + '</Value>';
filterString = filterString + '</Leq>';
filterString = filterString + '</And>';
filterString = filterString +'</Where>';
filterString = filterString + '</Query></View>';

Answer 1

Dealing with dates in Sharepoint is not that easy. Is your list a Calendar kind of list?

If yes, you should probably use <DateRangesOverlap> as well as <CalendarDate>.

At the end your whole query would look like:

<query>
  <Query>
    <Where>
      <DateRangesOverlap>
        <FieldRef Name="EstimatedDelivery" />
        <Value Type="DateTime">
          <Month />
        </Value>
      </DateRangesOverlap>
    </Where>
  </Query>
</query>
<viewFields>
  <ViewFields Properties='True'>
    <FieldRef Name="EstimatedDelivery" />
  </ViewFields>
</viewFields>
<rowLimit>0</rowLimit>
<queryOptions>
  <QueryOptions>
    <DateInUtc>False</DateInUtc>
    <Paging ListItemCollectionPositionNext=""></Paging>
    <IncludeAttachmentUrls>True</IncludeAttachmentUrls>
    <IncludeMandatoryColumns>False</IncludeMandatoryColumns>
    <ExpandUserField>False</ExpandUserField>
    <ViewAttributes Scope="Recursive"></ViewAttributes>
    <CalendarDate>2017-07-31</CalendarDate>
    <RecurrencePatternXMLVersion>v3</RecurrencePatternXMLVersion>
    <ExpandRecurrence>TRUE</ExpandRecurrence>
  </QueryOptions>
</queryOptions>

<DateRangesOverlap> can be used with some keywords like <Week /> or <Month /> (see this blog post):

<Month />: Performs consistently as expected, with the exception that its definition of "month" is more of a "month view." A month view consists of all of the weeks which contain days in that month. In other words, the month view for May 2012 begins on April 29, 2012, because May 1 is on a Tuesday and the "month view" consists of full weeks. Likewise, the May 2012 month view ends on June 5, 2012. This means that pulling all of the dates for May 2012 will return all events for April 29 – June 5.

However, even though April 29 is the beginning of the May month view, when passed as the CalendarDate, it will return the April month view. See below for how this affects date calculations based on pulling data for a given month.

And you can try 2017-07-31 for <CalendarDate> in the <QueryOptions>.

Answer 2

My calendar is a combination of lists displayed on a custom bootstrap and auto generated JavaScript page. I pull the data and the format it into custom form popovers as well as display a an auto generated div with link to the order entry. The standard SharePoint calendars didn't have enough functionality for my boss so he had me make one from scratch.
You have a great tip @AmyKdn, it provides a good work around but does not solve the issue i was having.