CAML Query Where dates are around or between two fields

Question

I'm having some difficulty thinking this through. I have columns in Sharepoint: Date_Start and Date_End

I want to query these fields based on a date range. So for instance, the user supplies two dates: 09/10/2017 and 09/15/2017 to search against.

Now I may have events in my custom list where Date_Start is 09/08/2017 and Date_End is 09/15/2017.

If I do a CAML query like the below, my events are not being found because the user specified beginning range is actually greater than the Date_Start date.

<Query>
    <Where>
        <And>
            <Geq>
                <FieldRef Name="Date_Start" />
                <Value Type="DateTime">2017-09-10</Value>
            </Geq>
            <Leq>
                <FieldRef Name="Date_End" />
                <Value Type="DateTime">2017-09-23</Value>
            </Leq>
        </And>
   </Where>
</Query>

The query above works just find if I had events where Date_Start is 09/08/2017 and Date_End is 09/15/2017. But this is not always the case.

How do I adjust my query to find all rows that fall within my searched range? I know I can change my And to an Or to get the data I need, but I feel like this might not be efficient with a ton of rows in the list.

Answer 1

You may try to restrict the query to end date only i.e. if the end date falls with the range, then CAML query should return all such rows.

<Query>
    <Where>
        <And>
            <Geq>
                <FieldRef Name="Date_End" />
                <Value Type="DateTime">2017-09-10</Value>
            </Geq>
            <Leq>
                <FieldRef Name="Date_End" />
                <Value Type="DateTime">2017-09-23</Value>
            </Leq>
        </And>
   </Where>
</Query>