Offset in CAML query not working

Question

I have the following items in a document library.

Item1
Name: Doc1
Created: 1st December 2014

Item2
Name: Doc2
Created: 15 Feb 2015

I am trying to write a CAML query that will bring those items which were uploaded more than or equal to 90 days ago based on today's date. So a CAML query, if run today, should bring Item1 but not Item2 because Item1 was uploaded 107 days ago while Item2 was uploaded 31 days ago.

This is my query but it is bringing both items. What am I doing wrong?

<Leq>
    <FieldRef Name='Created' />
        <Value Type='DateTime'>
            <Today Offset='-90' />
        </Value>
</Leq>

Answer 1

Hello,

Try something like this:

<Leq>
    <FieldRef Name='Created' />
        <Value Type='DateTime'>
            <Today OffsetDays='-90' />
        </Value>
</Leq>

Cheers,

Dan.

---

You can find my blog here: http://developertrack.blogspot.com

Answer 2

Hello,

Try something like this:

<Leq>
    <FieldRef Name='Created' />
        <Value Type='DateTime'>
            <Today OffsetDays='-90' />
        </Value>
</Leq>

Cheers,

Dan.

---

You can find my blog here: http://developertrack.blogspot.com

I tested this in CAML designer but it is giving same result i.e. both items are being displayed.

Answer 3

Hi,

Not sure then what the problem is - is this the only condition in the query?

Cheers,

Dan.

---

You can find my blog here: http://developertrack.blogspot.com

Answer 4

Hi,

According to your description, my understanding is that you want to get the documents created at more than or equal to 90days ago using CAML Query.

If you have debug with CAML Designer and also not getting the correct record, I suggest you can check if it has some other condition in the  CAML Query ? Some other condition such as "or" keyword may bring other record which not wanted.

Thanks

Best Regards

---

TechNet Community Support
Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact [e-mail].

Answer 5

Hi,

According to your description, my understanding is that you want to get the documents created at more than or equal to 90days ago using CAML Query.

If you have debug with CAML Designer and also not getting the correct record, I suggest you can check if it has some other condition in the  CAML Query ? Some other condition such as "or" keyword may bring other record which not wanted.

Thanks

Best Regards

---

TechNet Community Support
Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact [e-mail].

There is no other query. Could it be because I am not actually using Created field but I have created another field called TestDate whose type is Date & Time. So may be the offset only works with the default Created or Modified fields and not other fields? The query is actually the following (even if I change Offset to OffsetDays, same result)

<Where>
      <Lt>
         <FieldRef Name='TestDate' />
         <Value Type='DateTime'>
            <Today Offset='-90' />
         </Value>
      </Lt>
   </Where>

Or may be this is a bug because I am using SharePoint 2013 RTM version?

Answer 6

Hi,

try this

<Leq>
            <FieldRef Name='Created' />
            <Value Type='DateTime'>
               <Today Offset='-90' />
            </Value>
         </Leq>

Regards

---

If a post answers your question, please click Mark As Answer on that post and Vote as Helpful

Answer 7

Hi Frank,

I tried to query a custom date field with your caml and reproduce your issue.

I finally fingure out you need to use OffsetDays instead Offset like below and then it works as expected:

   <Where>
      <Leq>
         <FieldRef Name='testdate' />
         <Value Type='DateTime'>
            <Today OffsetDays='-90' />
         </Value>
      </Leq>
   </Where>

Thanks

Best Regards

---

TechNet Community Support
Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact [e-mail].