1

I want to get all the items that are created or modified from the last 4 hours from a SharePoint Online list using CSOM. I am using the below CAML query but i am not getting the items

DateTime startdate = DateTime.Now.AddHours(-4);
DateTime enddate = DateTime.Now;

 var finalquery = "<View><Query><Where><Or><And><Geq><FieldRef Name='Created' /><Value IncludeTimeValue='TRUE' Type='DateTime'>" + startdate.ToString("yyyy-MM-ddTHH:mm:ssZ") + "</Value></Geq><Leq><FieldRef Name='Created' /><Value IncludeTimeValue='TRUE' Type='DateTime'>" + enddate.ToString("yyyy-MM-ddTHH:mm:ssZ") + "</Value></Leq></And><And><Geq><FieldRef Name='Modified' /><Value IncludeTimeValue='TRUE' Type='DateTime'>" + startdate.ToString("yyyy-MM-ddTHH:mm:ssZ") + "</Value></Geq><Leq><FieldRef Name='Modified' /><Value IncludeTimeValue='TRUE' Type='DateTime'>" + enddate.ToString("yyyy-MM-ddTHH:mm:ssZ") + "</Value></Leq></And></Or></Where></Query></View>";

I am converting the dates to .ToString("yyyy-MM-ddTHH:mm:ssZ") while passing to CAML query And my CAML Query is as below while debugging .

<View><Query><Where><Or><And><Geq><FieldRef Name='Created' /><Value IncludeTimeValue='TRUE' Type='DateTime'>2018-05-01T11:12:56Z</Value></Geq><Leq><FieldRef Name='Created' /><Value IncludeTimeValue='TRUE' Type='DateTime'>2018-05-01T15:17:54Z</Value></Leq></And><And><Geq><FieldRef Name='Modified' /><Value IncludeTimeValue='TRUE' Type='DateTime'>2018-05-01T11:12:56Z</Value></Geq><Leq><FieldRef Name='Modified' /><Value IncludeTimeValue='TRUE' Type='DateTime'>2018-05-01T15:17:54Z</Value></Leq></And></Or></Where></Query></View>

But all the items are created and modified recently enter image description here

Can any one help me on this please.

  • Which type of help you want? – Dikesh Gandhi May 1 '18 at 10:58
  • I want the know what is the error in the query.. – TARUN May 1 '18 at 11:04
1

Adjust your tines backward or forward a few hours as a test. SharePoint Online web services use UTC internally. London time. I had some issues with JavaScript created dates and the REST API awhile back. I'll see if I can find my notes...

  • After adjusting the time to 8 hours, the query is working fine. But i want to know why it is not working for 4 hours. Where to check the Time settings, both the Site and Azure function are in US time – TARUN May 4 '18 at 11:06
  • Internally SharePoint Online uses GMT / Universal Time (UTC) (same time as London). The displayed times (what displays in pages) are adjusted at the Tenant level, at the site level ( Site Administration, Regional settings ) and/or at the user level ( My Account, Settings, Language and time zone ) Almost all system level work is GMT/UTC. (REST API, search, etc.) – Mike Smith - MCT - MVP May 4 '18 at 13:42
0

Without having really tried the query it looks valid.

But what are you using it for? Depending on the usage you may need to remove <View> and <Query> (and the corresponding closing tags)

0

As Per Jakobsen said , your query looks valid , i made a quick test on a console application to test and works, it would be more helpful if you added your full function to your question so we can help you determine what went wrong, but i'm adding the test sample below for your reference.

   string siteUrl = "https://server/sites/xyz";
   ClientContext clientContext = new ClientContext(siteUrl);
   List oList = clientContext.Web.Lists.GetByTitle("testv");
   CamlQuery camlQuery = new CamlQuery();
   camlQuery.ViewXml = "<View><Query><Where><Or><And><Geq><FieldRef Name='Created' /><Value IncludeTimeValue='TRUE' Type='DateTime'>2018-05-01T11:12:56Z</Value></Geq><Leq><FieldRef Name='Created' /><Value IncludeTimeValue='TRUE' Type='DateTime'>2018-05-01T15:17:54Z</Value></Leq></And><And><Geq><FieldRef Name='Modified' /><Value IncludeTimeValue='TRUE' Type='DateTime'>2018-05-01T11:12:56Z</Value></Geq><Leq><FieldRef Name='Modified' /><Value IncludeTimeValue='TRUE' Type='DateTime'>2018-05-01T15:17:54Z</Value></Leq></And></Or></Where></Query></View>";

   ListItemCollection collListItem = oList.GetItems(camlQuery);
   clientContext.Load(collListItem);
   clientContext.ExecuteQuery();
  • I am using my query in azure function – TARUN May 1 '18 at 12:11

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.