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

Question

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>

Answer 1

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;

Answer 2

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>

Answer 3

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.