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

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 ?

share|improve this question
add comment

2 Answers 2

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

       <Where>
      <Or>
         <Geq>
            <FieldRef Name='StartDate' />
            <Value Type='DateTime'>
               <Today Offset='30' />
            </Value>
         </Geq>
         <Leq>
            <FieldRef Name='StartDate' />
            <Value Type='DateTime'>
               <Today Offset='-60' />
            </Value>
         </Leq>
      </Or>
   </Where>
share|improve this answer
    
I assume you should change "Geq" to "Leq", and then "Eq" to "Geq" :) –  Andrey Markeev Jun 5 '12 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. –  PirateEric Jun 5 '12 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 Jun 6 '12 at 7:04
    
his happens because the offset applies to the current date and not the StartDate (i.e. list field). –  suhas Jun 6 '12 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? –  PirateEric Jun 6 '12 at 14:21
show 2 more comments
up vote 0 down vote accepted

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.

share|improve this answer
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.