4

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?

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

1 Answer 1

16

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.

1
  • Thank you! You would think that the 'Z' at the end of the datetime value would force it to do a UTC comparison, but it will ignore that. Only StorageTZ forces it. I struggled with this for a long time before seeing your answer. May 5, 2023 at 10:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.