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

4 Answers 4

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.

5
  • I guess U2U is only for 2007 and not for 2010 !!
    – Jithu
    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... 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
    – Deepu Nair
    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.
    – Jithu
    Dec 26, 2011 at 11:35
  • Thanks, Got it worked, Updated my question to include changes !!
    – Jithu
    Dec 26, 2011 at 11:55
4

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

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 ?
    – Jithu
    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.
    – Jithu
    Dec 26, 2011 at 11:35
  • hi... you can make it as... System.DateTime.Now.AddDays(7).toString("yyyy-MM-dd")...
    – Strider
    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).

4
  • Thanks, then what now I should do with my date ?
    – Jithu
    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.
    – Jithu
    Dec 26, 2011 at 11:35
  • Thanks, RUSSELL got it worked see the updated question !!
    – Jithu
    Dec 26, 2011 at 11:56
  • Glad to hear you got it to work :)
    – Russell
    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)

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

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.