none
CAML Query Referencing List Value RRS feed

  • Question

  • I'm trying to query for a single list item where today's date is in between the value in the list and the value in the list + 7 days.  I see all sorts of links regarding how to query for +/- day offsets of today's date.  Can I do the same to query for offsets of the value in the list in CAML without resorting to workarounds?  I really don't want to have to grab the entire list from Sharepoint via the Lists web service and filter it after the fact.  Please let me know.  Thanks.

    Dale Gantz

     

     

    Wednesday, September 28, 2011 3:13 PM

Answers

  • Hi Dale,

    Your requirement says "today's date is in between the date in the list and date in the list +7 days"

    I don't see the straight way implementation using CAML. So I am using some math to achieve this within the boundaries using CAML

    x - date in the list

    today - today's date

    The equation equivalent of the above statement is

    x < today < x+7   ==> x<today and today<x+7

    subract today

    x-today<0<x+7-today

    subtract x

    -today<-x<7-today

    Multiply with '-'

    today>x>today-7  ==> x<today  and (x>today-7  ==> today<x+7)

    <Query>
     <Where>
      <And>
       <Lt>
        <FieldRef Name="list date" />
        <Value  Type="DateTime">[Today]</Value>
       </Lt>
       <Gt>
        <FieldRef Name="list date" />
        <Value Type="DateTime">[Today-7Day(s)]</Value>
       </Gt>
      </And>
     </Where>
    </Query>

     

    Hope this helps.

    Wednesday, September 28, 2011 8:10 PM
  • so you have the following condition: x["Date"] < now < x["Date"] + 7. It is equivalent to: now - 7 < x["Date"] < now. So you need to combine 2 conditions in CAML: x["Date"] > now - 7 AND x["Date"] < now. Something like this:

     

    <Where>
      <And>
        <Gt>
          <FieldRef Name="Date" />
          <Value Type="DateTime">
            <Today />
          </Value>
        </Gt>
        <Lt>
          <FieldRef Name="Date" />
          <Value Type="DateTime">
            <Today Offset="-7" />
          </Value>
        </Lt>
      </And>
    </Where>
    


    Also you can use Camlex:

    var dt = DateTime.Now;
    string s =
        Camlex.Query().Where(x => (DateTime) x["Date"] > dt.Subtract(TimeSpan.FromDays(7)) && (DateTime) x["Date"] < dt).ToString();
    


    It will generate the following CAML automatically:

    <Where>
      <And>
        <Gt>
          <FieldRef Name="Date" />
          <Value Type="DateTime">2011-09-21T23:16:12Z</Value>
        </Gt>
        <Lt>
          <FieldRef Name="Date" />
          <Value Type="DateTime">2011-09-28T23:16:12Z</Value>
        </Lt>
      </And>
    </Where>
    


     


    Blog - http://sadomovalex.blogspot.com
    CAML via C# - http://camlex.codeplex.com
    Wednesday, September 28, 2011 8:21 PM

All replies

  • Hi Dale,

    Your requirement says "today's date is in between the date in the list and date in the list +7 days"

    I don't see the straight way implementation using CAML. So I am using some math to achieve this within the boundaries using CAML

    x - date in the list

    today - today's date

    The equation equivalent of the above statement is

    x < today < x+7   ==> x<today and today<x+7

    subract today

    x-today<0<x+7-today

    subtract x

    -today<-x<7-today

    Multiply with '-'

    today>x>today-7  ==> x<today  and (x>today-7  ==> today<x+7)

    <Query>
     <Where>
      <And>
       <Lt>
        <FieldRef Name="list date" />
        <Value  Type="DateTime">[Today]</Value>
       </Lt>
       <Gt>
        <FieldRef Name="list date" />
        <Value Type="DateTime">[Today-7Day(s)]</Value>
       </Gt>
      </And>
     </Where>
    </Query>

     

    Hope this helps.

    Wednesday, September 28, 2011 8:10 PM
  • so you have the following condition: x["Date"] < now < x["Date"] + 7. It is equivalent to: now - 7 < x["Date"] < now. So you need to combine 2 conditions in CAML: x["Date"] > now - 7 AND x["Date"] < now. Something like this:

     

    <Where>
      <And>
        <Gt>
          <FieldRef Name="Date" />
          <Value Type="DateTime">
            <Today />
          </Value>
        </Gt>
        <Lt>
          <FieldRef Name="Date" />
          <Value Type="DateTime">
            <Today Offset="-7" />
          </Value>
        </Lt>
      </And>
    </Where>
    


    Also you can use Camlex:

    var dt = DateTime.Now;
    string s =
        Camlex.Query().Where(x => (DateTime) x["Date"] > dt.Subtract(TimeSpan.FromDays(7)) && (DateTime) x["Date"] < dt).ToString();
    


    It will generate the following CAML automatically:

    <Where>
      <And>
        <Gt>
          <FieldRef Name="Date" />
          <Value Type="DateTime">2011-09-21T23:16:12Z</Value>
        </Gt>
        <Lt>
          <FieldRef Name="Date" />
          <Value Type="DateTime">2011-09-28T23:16:12Z</Value>
        </Lt>
      </And>
    </Where>
    


     


    Blog - http://sadomovalex.blogspot.com
    CAML via C# - http://camlex.codeplex.com
    Wednesday, September 28, 2011 8:21 PM