Caml Query - Unable to get data by date range

Question

I have a list "List-A" which have an item with following details:

StartDate: 2021-08-31T22:00:00Z [In list view- 09/1/2021, type- DateOnly]

EndDate: 2021-09-29T22:00:00Z [In list view- 09/30/2021, Type- DateOnly]

From console application I am passing following caml query to pick the items (sharing Where clause only) :

<And>
        <Geq>
          <FieldRef Name='StartDate' />
          <Value Type='Date' >2021-08-31T22:00:00Z</Value>
        </Geq>
        <Leq>
          <FieldRef Name='EndDate' />
          <Value Type='Date' >2021-09-29T23:59:59Z</Value>
        </Leq>
      </And>

Its not returning above item from the list. But if I pass date which exactly matches with the date in list view(2021-09-30T23:59:59Z), then its returning the item.

It seems caml query is comparing value which is in list view and not in UTC one. Am I missing something here?

Answer 1

Modify your CAML Query to

<Geq>
     <FieldRef Name='StartDate' />
     <Value IncludeTimeValue='TRUE' Type='DateTime' StorageTZ='TRUE'>2021-08-31T22:00:00Z</Value>
</Geq>

Here, we have added the StorageTZ=TRUE attribute which tells SharePoint to do the comparison using the native storage time zone (UTC time) of the field value since all time values of date time fields are stored using UTC time.