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

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!

share|improve this question
    
@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 '13 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 –  Muhammad Raja Jan 7 '13 at 23:39
add comment

2 Answers

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

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

hope it helps!

share|improve this answer
    
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 '13 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 Sharepoint Jan 9 '13 at 13:40
    
added an edit that contains dynamic offsetdays but only in c# –  ali Sharepoint Jan 9 '13 at 13:49
add comment

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

share|improve this answer
    
For this special case seems there should be some other sollution, the result set is still off –  stoneclub Jan 8 '13 at 9:09
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.