Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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

share|improve this question
add comment

4 Answers

up vote 7 down vote accepted

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.

share|improve this answer
    
Thanks Sven, Will Try this :) –  Jithu Dec 26 '11 at 10:57
    
I guess U2U is only for 2007 and not for 2010 !! –  Jithu Dec 26 '11 at 10:59
1  
No, you can still use this tool, but you need to connect with the webservices - otherwise it won't work... –  Sven Gillis Dec 26 '11 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 '11 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 '11 at 11:35
show 1 more comment

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

share|improve this answer
    
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 '11 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 '11 at 11:35
    
hi... you can make it as... System.DateTime.Now.AddDays(7).toString("yyyy-MM-dd")... –  Strider Dec 26 '11 at 11:49
    
Thanks Paddy, got it worked Plz see update !! –  Jithu Dec 26 '11 at 11:53
add comment

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

share|improve this answer
    
Thanks, then what now I should do with my date ? –  Jithu Dec 26 '11 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 '11 at 11:35
    
Thanks, RUSSELL got it worked see the updated question !! –  Jithu Dec 26 '11 at 11:56
    
Glad to hear you got it to work :) –  Russell Dec 26 '11 at 21:36
add comment

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

share|improve this answer
    
Thanks, Shankar Got It worked!! Plz see the updated answer!! –  Jithu Dec 26 '11 at 12:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.