CAML Query based on Created and Modified columns in SharePoint List

Question

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

Can any one help me on this please.

Answer 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...

Answer 2

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)

Answer 3

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();