CAML query not working

Question

I have written following CAML query to Retrieve items which are due in 7 days, It doesn't seems to be working, Please let me know what I missed ?

string internalEndDate = web.Lists["List Name"].Fields["Next End date"].InternalName;

<Where>
  <AND>
  <Leq>
   <FieldRef Name ='" + internalEndDate + "'/> 
   <Value Type ='DateTime'>" + System.DateTime.Now.AddDays(7) + "</Value>
  </Leq> 

  </AND>
 </Where>
<OrderBy>
  <FieldRef Name='" + internalClosure + "' Ascending='False' />
</OrderBy>";

The above query does't seem to be working, Also how can I add along with this condition, the data has to be greater than or equal to today. Thanks!!

UPDATE : (As Per Sven Gillis advise, Worked !!)

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


<Where>
      <AND>
      <Leq>
       <FieldRef Name ='" + internalEndDate + "'/> 
       <Value Type ='DateTime' IncludeTimeValue='FALSE'>" + Iso8601DateTime  + "</Value>
      </Leq> 
       <Geq>
       <FieldRef Name ='" + internalEndDate + "'/> 
       <Value Type ='DateTime' IncludeTimeValue='FALSE'>>" + Iso8601Today + "</Value>
      </Geq> 
      </AND>
 </Where>
  <OrderBy>
      <FieldRef Name='" + internalClosure + "' Ascending='False' />
  </OrderBy>";

And used, tag to filter the date greater than today!!

Got It Worked !!

Answer 1

You have to convert your DateTime object to a ISO8601 format. You can use the SPUtility class. I always use the U2U Caml Query builder to test my queries.

Answer 2

You can even pass the date in the format yyyy-MM-dd ... :)

Answer 3

Sven Gillis has the correct answer that the DateTime object needs to be in the ISO format required.

I want to elaborate on why your code specifically is not working as you expected. You are concatenating the DateTime object (the output of DateTime.Now.AddDays(7)) with other string objects. This will call the default .ToString() method of DateTime. This will display the date/time in the current culture of the process the code is running in (ie. the SharePoint server).

Answer 4

I think the date should be in Sortable DateTi­me Pattern like "2011-12-26T00:00:00". Try this String.Format("{0:s}", Date)