2

How do I get files using CAML query from SharePoint Online site filtering them by Modified date in UTC?

When documents are created or updated on site's list they get the Modified DateTime set to site's local time which is (UTC - 8:00) Pacific Time by default.

Now, when I'm fetching items from that list using the query below:

<View>
  <Query>
    <Where>
      <Gt>
        <FieldRef Name='Modified' />
        <Value IncludeTimeValue='TRUE' Type='DateTime'>2018-06-28T07:07:09Z</Value>
      </Gt>
    </Where>
  </Query>
  <RowLimit>100</RowLimit>
</View>

they are getting compared using their local time which is UTC - 8:00 in this particular case but can be changed anytime to some other value and I can't possibly even know about it.

So, how can I tell SharePoint, using the CAML query, that I'm passing my DateTime as UTC and expect it to compare items' Modified DateTime in UTC as well?

  • Modify it to <Value IncludeTimeValue='TRUE' Type='DateTime' StorageTZ='TRUE'>2018-06-28T07:07:09Z</Value> and check ? – Gautam Sheth Jun 28 '18 at 17:28
  • @GautamSheth Great, it works. Post an answer and I'll accept it. – Szybki Jun 28 '18 at 18:05
8

Modify your CAML Query to <Value IncludeTimeValue='TRUE' Type='DateTime' StorageTZ='TRUE'>2018-06-28T07:07:09Z</Value>

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.

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.