How to pass a string(date) in a caml query to get listitem based on datetime type of field

Question

I want to pass a string(date) in a caml query to get listitem based on datetime type of field. I am not getting any value in return. Please suggest. Below is my code:

` private SP.ListItem FindListItem(List selectedList, Field field, string value, ClientContext clientContext)
    {
        SP.ListItem listItem = null;
        CamlQuery camlQuery = new CamlQuery();
        if (field.TypeAsString == "DateTime")
        {
            DateTime datevalue = Convert.ToDateTime(value);
            camlQuery.ViewXml = @"<View><Query><Where><Eq><FieldRef Name=" + field.InternalName + @"/><Value Type=" + field.TypeAsString + @">" + datevalue + @"</Value></Eq></Where></Query></View>";                               

        SP.ListItemCollection items = selectedList.GetItems(camlQuery);
        clientContext.Load<SP.ListItemCollection>(items);
        clientContext.ExecuteQuery();
        if (items.Count > 0)
            listItem = items[0];

        return listItem;
    }`

Answer 1

You have to convert your DateTime object to a ISO8601 format. You can use the SPUtility class.

Example:

string datevalue = SPUtility.CreateISO8601DateTimeFromSystemDateTime(Convert.ToDateTime(value));

Source

---

As far as Client-Side Object Model (CSOM), versus Server Side,

Server-Side

string datevalue = SPUtility.CreateISO8601DateTimeFromSystemDateTime(Convert.ToDateTime(value));

Client-Side

You would need to use the string formatter,

string datevalue = Convert.ToDateTime(value).ToString("yyyy-MM-ddTHH:mm:ssZ");

---

As far as the performance optimizations, suggested by the other individual, there are some suggestions, at the following page using CSOM, Point #5, on how to reduce the number of round trips to the server, and increase performance.

Answer 2

You have to convert your DateTime object to a ISO8601 format using SPUtility class. and then pass it to Caml query.

string Iso8601Today = XmlConvert.ToString(DateTime.Now, XmlDateTimeSerializationMode.Local);

Replace DateTime.Now with your "value".

Source

Answer 3

get listitems without query , clientContext.ExecuteQuery(); after do your limitation by converting your string to date. this can be reduce performance.

Answer 4

I usually use a function to convert a date to ISO Date.

function ISODateString(d){
  function pad(n){return n<10 ? '0'+n : n}
  return d.getUTCFullYear()+'-'
      + pad(d.getUTCMonth()+1)+'-'
      + pad(d.getUTCDate())+'T'
      + pad(d.getUTCHours())+':'
      + pad(d.getUTCMinutes())+':'
      + pad(d.getUTCSeconds())+'Z'
}

Then you use like that:

var desde = ISODateString(Pdesde);
var hasta = ISODateString(Phasta);

And the CAML is:

'<Query><Where><And><Leq><FieldRef Name="Fecha"/><Value Type="DateTime" IncludeTimeValue="TRUE">' + hasta + '</Value></Leq><Geq><FieldRef Name="Fecha"/><Value Type="DateTime" IncludeTimeValue="FALSE">' + desde + '</Value></Geq></And></Where></Query>'