Using part of date in CAML query

Question

I want to create new view for my list. The list contains a column called Year, which is a number and contains, as name suggests, year. Eg.:

I would like create a view, where I would display only last 3 years. The <Query> element of my List Definition is:

<Query>
  <Where>
    <Geq>
      <FieldRef Name="Year" />
      <Value Type="Number"> ???? </Value>
    </Geq>
  </Where>
</Query>

And it gets tricky here. How to write a condition, that a number should be greater that current year minus 3? I thought it would work like in UI, so I could use:

YEAR([Today])-3

Renzo · Accepted Answer · 2012-06-19 13:57:40Z

1

Maybe you can you use the OffsetDays attribute for this? (all depends on how exact the 3 years should be) as there is no way you can do leap years this way. Here is how do it using days in case.

<Query>
  <Where>
    <Geq>
      <FieldRef Name="Year" />
         <Today OffsetDays="-1" /> //amount of days to subtract
    </Geq>
  </Where>
</Query>

Hope this helps

answered Jun 19, 2012 at 13:57

Renzo

2,0971 gold badge14 silver badges14 bronze badges

Add a comment |

Waqas Sarwar MVP · Accepted Answer · 2014-09-30 18:29:41Z

What I ended up doing was to create additional calculated column that returns either "yes" or "no" values. In your case the formula would be similar to:

=IF([Year]>=TEXT(YEAR([Today])-3,"0"),"Yes","No")

In order to be able to use [Today] in the formula you need to create a single line of text field called "Today". After your calculated column is created remove the "Today" column (here is the original post for the process).

Once you have the calculated column created, your query would look similar to this:

<Query>
  <Where>
    <Geq>
      <FieldRef Name="<Calc Field Here>" Type="Choice"/>
        <Value Type="Choice">Yes</Value>
    </Geq>
  </Where>
</Query>

Stack Exchange Network

Using part of date in CAML query

2 Answers 2

Your Answer

Hot Network Questions

Using part of date in CAML query

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions