CAML OffsetDays

Question

please help if it possible that CAML OffsetDays is not working in DvWP?

I use the following CAML within a DVWP:

<View><Query>
<Where><And>
<Lt>
<FieldRef Name="Date"/><Value Type="DateTime">{Param1}</Value>
</Lt>
<Gt>
<FieldRef Name="First_time><Value Type="DateTime">{Param1} OffsetDays="-6"</Value>
</Gt>
</And></Where>
<OrderBy>
<FieldRef Name="Category_Level" Ascending="TRUE"/>
<FieldRef Name="Rank" Ascending="FALSE"/>
</OrderBy>
</Query></View>

If I select Param1 = #1/2/2013#, it returns all items where First_time >= #1/1/2013#, but ignores First_time = #12/30/2012# or First_time = #12/31/2012#, accidentally?

I checked and it seems that even if I include the OffsetDays in CAML, the query ignores the tag in the DVWP.

Please help, how it could be resolved. Thanks!

Answer 1

You can try the following! I always thought that offsetdays need to be in its relative tag hence why its not working properly!

<View><Query>
<Where><And>
<Lt>
<FieldRef Name="Date"/><Value Type="DateTime">{Param1}</Value>
</Lt>
<Gt>
<FieldRef Name="First_time><Value Type="DateTime">{Param1}<Today OffsetDays="-6"/></Value>
</Gt>
</And></Where>
<OrderBy>
<FieldRef Name="Category_Level" Ascending="TRUE"/>
<FieldRef Name="Rank" Ascending="FALSE"/>
</OrderBy>
</Query></View>

for dynamic offsetdays you need to do it the c# way

 //this is the varible that you can have dynamic 
 string offSetByDays = "-1";

 SpQuery q = new SPQuery();
 q.Query= string.Format(
 "
  <View><Query>
  <Where><And>
  <Lt>
  <FieldRef Name='Date'/><Value Type='DateTime'>{Param1}</Value>
  </Lt>
  <Gt>
  <FieldRef Name='First_time><Value Type='DateTime'>{Param1}<Today OffsetDays={0}/></Value>
   </Gt>
   </And></Where>
   <OrderBy>
   <FieldRef Name='Category_Level' Ascending='TRUE'/>
   <FieldRef Name='Rank' Ascending='FALSE'/>
   </OrderBy>
   </Query></View>
 ",offSetByDays);

more info on this can be found here ;)

https://stackoverflow.com/questions/3045294/how-to-write-the-spquery-for-getting-all-data-of-contact-list

hope it helps!

Answer 2

Using c# I would do.

  SpQuery q = new SPQuery();
  q.Query=
     "
      <Where><And>
         <Lt>
              <FieldRef Name="Date"/><Value Type="DateTime">{Param1}</Value>
         </Lt>
         <Gt>
              <FieldRef Name="First_time><Value Type="DateTime" OffsetDays="-6"> 1/2/2013</Value>
         </Gt>
       </And></Where>
    "

*note: c# multi-line strings can be contat() by using "+"