1

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!

2
  • @TimeToShine How could that help? I do not have problem to run the code, the result is out of expectation. Seems the problem is only if changing years.
    – stoneclub
    Jan 7, 2013 at 23:37
  • I didn't really worked with CAML that much but used query builder to build queries so thought it might help you Jan 7, 2013 at 23:39

2 Answers 2

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!

3
  • I have double checked and this code made the trick. Last question tough, is it possible to use parameter instead of "-6"? I mean I tried the following code as well: code<Today OffsetDays="-{Param2}"/>code, but somehow it is not working. I am wondering, how to bind parameter within the relative tag.
    – stoneclub
    Jan 9, 2013 at 12:49
  • unfortunatly not that i know of, unless you make a webpart and add the query in from there you can dynamically add in the offsetdays! Like user13186 pointed out doing it in c# webpart with my query but with a varible that contains the offsetdays would work a treat :)
    – Ali Jafer
    Jan 9, 2013 at 13:40
  • added an edit that contains dynamic offsetdays but only in c#
    – Ali Jafer
    Jan 9, 2013 at 13:49
0

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 "+"

1
  • For this special case seems there should be some other sollution, the result set is still off
    – stoneclub
    Jan 8, 2013 at 9:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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