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?

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

1 Answer 1

Reset to default
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.

Improve this answer
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. Commented May 5, 2023 at 10:25

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.