CODEWITHSUNDEEP
sharepoint-2010caml
Sivakumar Piratheeban
Sivakumar Piratheeban

Reputation: 493

Create CAML query with have a value field with SharePoint column-SharePoint 2010

I have a SharePoint list. It has two columns Start date and End date. I need to query and get data if (start date + 7 days> End date). Through CAML builder its not possible to have a SharePoint column on value node and build the query. Any idea? I have tried below. But not working.

<Query>
   <Where>
      <Eq>
         <FieldRef Name='EndDate' />
         <Value IncludeTimeValue='TRUE' Type='DateTime'><StartDate+7/></Value>
      </Eq>
   </Where>
</Query>

Upvotes: 0

Views: 2566

Answers (3)

Mike
Mike

Reputation: 807

I would recommend creating a calculated column to calculate the difference between the start and end date:

=DATEDIF([Created], [EndDate],"d")

and then in your camlquery filter by greater or equal than 7 days

<Query>
   <Where>
      <Geq>
         <FieldRef Name="DateDiff"/>
         <Value IncludeTimeValue='TRUE' Type='Number'>7<Value>
      </Geq>
   </Where>
</Query>

Upvotes: 1

Jan Vanek
Jan Vanek

Reputation: 889

You can not compare two field of item to each other in CAML query. You can either create computed field and do the compare in it or you can use LINQ. Something like this:

SPList tasks = SPContext.Current.Web.Lists["tasks"];
var ts = from t in tasks.Items.OfType<SPListItem>() where t["DueDate"] == null || (DateTime)t["Modified"] > (DateTime)t["DueDate"] select t;

Upvotes: 1

Nikunj
Nikunj

Reputation: 556

Instead of EQ you should use GeQ, LeQ http://social.msdn.microsoft.com/Forums/sharepoint/en-US/fed59f8e-72e2-46e2-9329-460fd65d7536/caml-query-datetime?forum=sharepointdevelopmentlegacy

https://sharepoint.stackexchange.com/questions/15770/caml-query-with-date-range

i haven't tried it, my self.

Upvotes: 0

Related Questions