1

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.

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>
| improve this answer | |

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.