6

In SharePoint 2010, Inned to get items from a list based on a condition. Considering one of the fields to be 'EventDate' of type DateTime, the condition is:

(EventDate - 60 Days) <= Today <= (EventDate + 30Days).

Note that the values 60 and 30 are not static.

To rephrase it in words, I need to show the list item if the event occurs 60 days from today or has occurred 30 days from today.

Hence, I need a rolling window on the EventDate. The OffsetDate attribute on Today element in CAML provides a rolling window on the Current date. Is it possible to achieve this in CAML? If not how do I filter the list items based on this condition ?

Improve this question

4 Answers 4

Reset to default
14

You'd use something like this, using the BIWUG CamlDesigner:

       <Where>
      <Or>
         <Geq>
            <FieldRef Name='StartDate' />
            <Value Type='DateTime'>
               <Today OffsetDays='30' />
            </Value>
         </Geq>
         <Leq>
            <FieldRef Name='StartDate' />
            <Value Type='DateTime'>
               <Today OffsetDays='-60' />
            </Value>
         </Leq>
      </Or>
   </Where>
Improve this answer
9
  • I assume you should change "Geq" to "Leq", and then "Eq" to "Geq" :)
    – Andrey Markeev
    Commented Jun 5, 2012 at 17:54
  • CAML output wasn't updating, that should do it. greater than or equal today + 30 and less than or equal to today - 60.
    – Eric Alexander
    Commented Jun 5, 2012 at 18:26
  • the above query will not give the correct result. Let me give an example: If the StartDate is 2-Jun, then the date ranges are 2-Apr( 2/Jun - 60) and 2-Jul(2/Jun + 30) [for the sake of brevity I am considering 30 day months]. Hence if the condition 2-Apr <= current date <= 2-Jul satisfies then the list item should be obtained. Hence, on 3-jul this list item should not be obtained. If we apply the above query with current date as 3-Jul, then the date ranges are: 3-May (3/Jul - 60) to 3-Aug (3/Jul + 30) and 2-jun lies between the date ranges. Hence on 3-Jul this list item will be obtained.
    – suhas
    Commented Jun 6, 2012 at 7:04
  • his happens because the offset applies to the current date and not the StartDate (i.e. list field).
    – suhas
    Commented Jun 6, 2012 at 7:04
  • That's the only way I know it can be done, based on evaluating the current date. Are you trying to pass in a value as a filter to something that dynamically?
    – Eric Alexander
    Commented Jun 6, 2012 at 14:21
5

Update for SharePoint 2013: although MSDN lists Offset as an attribute for Today element (https://msdn.microsoft.com/en-us/library/office/ms460496.aspx), when customizing the CAML on a list view in SharePoint Designer 2013 I tried the Offset attribute but it didn't work. I used the "OffsetDays" attribute (not documented in MSDN) and the query returned the desired output (items with date field up to 5 days from the current date).

Improve this answer
4

Marked answer won't work (at least in SP 2010) unless you change the Today Offset bit to "Today OffsetDays" thus having:

<Where>
  <And>
     <Geq>
        <FieldRef Name='EventDate' />
        <Value Type='DateTime'>
           <Today OffsetDays='-30' />
        </Value>
     </Geq>
     <Leq>
        <FieldRef Name='EventDate' />
        <Value Type='DateTime'>
           <Today OffsetDays='60' />
        </Value>
     </Leq>
  </And>

Cheers!

Improve this answer
2

I found the query that worked:

    <Where>
      <And>
         <Geq>
            <FieldRef Name='EventDate' />
            <Value Type='DateTime'>
               <Today Offset='-30' />
            </Value>
         </Geq>
         <Leq>
            <FieldRef Name='EventDate' />
            <Value Type='DateTime'>
               <Today Offset='60' />
            </Value>
         </Leq>
      </And>
   </Where>

It was only a small change i.e. to subtract the EndDate and add the StartDate to the current date.Thank you @PirateEric for your help.

Improve this answer
3
  • Didn't work for me util I changed to <Today OffsetDays='-30' />
    – Jaime Arroyo Garcia
    Commented Jul 6, 2016 at 10:40
  • Also for me it did not work, had to use OffsetDays as Jaime Arroyo Garcia.
    – Emaborsa
    Commented Sep 26, 2016 at 13:11
  • Thanks Suhas it worked like a charm.
    – Viral
    Commented Jan 16, 2020 at 4:12

Your Answer

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

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