5

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 !!

Improve this question

4 Answers 4

Reset to default
8

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.

Improve this answer
5
  • I guess U2U is only for 2007 and not for 2010 !! Commented Dec 26, 2011 at 10:59
  • 1
    No, you can still use this tool, but you need to connect with the webservices - otherwise it won't work... Commented Dec 26, 2011 at 11:02
  • 1
    I think you need to install the client app. Did u try downloading and installing from this link? - u2u.net/Tools/wincamlquerybuilder/CamlQueryBuilder.aspx Commented Dec 26, 2011 at 11:33
  • I even tried with this, query.Query = @"<Where><Leq><FieldRef Name ='" + internalClosure + "'/> <Value Type ='DateTime'> <Today OffsetDays='7'> </Value></Leq></Where>"; Now getting a SP exception Microsoft.SharePoint.SPException: Cannot complete this action. Commented Dec 26, 2011 at 11:35
  • Thanks, Got it worked, Updated my question to include changes !! Commented Dec 26, 2011 at 11:55
4

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

Improve this answer
3
  • Hi Paddy I am using this code to pass the date, System.DateTime.Now.AddDays(7), Now how will I make it that way ? Commented Dec 26, 2011 at 11:26
  • I even tried with this, query.Query = @"<Where><Leq><FieldRef Name ='" + internalClosure + "'/> <Value Type ='DateTime'> <Today OffsetDays='7'> </Value></Leq></Where>"; Now getting a SP exception Microsoft.SharePoint.SPException: Cannot complete this action. Commented Dec 26, 2011 at 11:35
  • hi... you can make it as... System.DateTime.Now.AddDays(7).toString("yyyy-MM-dd")... Commented Dec 26, 2011 at 11:49
4

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

Improve this answer
4
  • Thanks, then what now I should do with my date ? Commented Dec 26, 2011 at 11:27
  • I even tried with this, query.Query = @"<Where><Leq><FieldRef Name ='" + internalClosure + "'/> <Value Type ='DateTime'> <Today OffsetDays='7'> </Value></Leq></Where>"; Now getting a SP exception Microsoft.SharePoint.SPException: Cannot complete this action. Commented Dec 26, 2011 at 11:35
  • Thanks, RUSSELL got it worked see the updated question !! Commented Dec 26, 2011 at 11:56
  • Glad to hear you got it to work :) Commented Dec 26, 2011 at 21:36
3

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

Improve this answer
1
  • Thanks, Shankar Got It worked!! Plz see the updated answer!! Commented Dec 26, 2011 at 12:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.