Using Date offset in CAML query

Question

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 ?

Eric Alexander · Answer 1 · 2016-10-19T13:04:23.207

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>

edited Oct 19 '16 at 13:04

answered Jun 05 '12 at 16:03

Eric Alexander

43,293
10
53
93

I assume you should change "Geq" to "Leq", and then "Eq" to "Geq" :) – Andrey Markeev Jun 05 '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. – Eric Alexander Jun 05 '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 06 '12 at 07:04
his happens because the offset applies to the current date and not the StartDate (i.e. list field). – suhas Jun 06 '12 at 07: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 Jun 06 '12 at 14:21
yes...I found a workaround by adding extra columns. – suhas Jun 07 '12 at 09:07
Instead of Offset use OffsetDays – nbi Oct 19 '16 at 06:11
@nbi thanks for bringing that to my attention, I've updated the answer to be up to snuff, hope that changes your mind on the down vote. Alternatively, you can propose an edit to the question in the future. – Eric Alexander Oct 19 '16 at 13:05
@EricAlexander Upvoted. – nbi Oct 20 '16 at 06:50

score 5 · Answer 2 · edited Jul 07 '15 at 19:33

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

score 4 · Answer 3 · answered Jul 06 '16 at 10:40

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!

score 2 · Accepted Answer · answered Jun 07 '12 at 11:50

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.

answered Jun 07 '12 at 11:50

suhas

394
1
5
15

Didn't work for me util I changed to – Jaime Arroyo Garcia Jul 06 '16 at 10:40
Also for me it did not work, had to use OffsetDays as Jaime Arroyo Garcia. – Emaborsa Sep 26 '16 at 13:11
Thanks Suhas it worked like a charm. – Viral Jan 16 '20 at 04:12

Using Date offset in CAML query

4 Answers4

Linked