9

I'm writing an SPSiteDataQuery and I am having issues with the Query property. I need to return all results where the Record_x0020_Yr_x0020_End_x0020_Date is between 12/31 of the current year and 90 days prior.

Example: If I ran this query now, I should get all items where the date lies between 9/31/2011-12/31/2011. If I ran the query at any point next year, I should get 9/31/2012-12/31/2012. Here's what I have thus far:

query.Query = string.Format(@"
<Where>
    <Eq>
        <FieldRef Name='Record_x0020_Yr_x0020_End_x0020_Date' />
        <Value Type='DateTime' IncludeTimeValue='FALSE'>{0}</Value>
    </Eq>
</Where>", new DateTime(DateTime.Now.Year, 12, 31).ToString("yyyy-MM-dd"));

This gives me all those whose date is on the end of the year, but how do I add the range?

12

Download a CAML query builder tool such as this one by U2U to help you get the syntax right.

Using that tool this is what I get for Start Date >= 1/Jan/2011 AND StartDate <= 31/Dec/2011 - you can adapt this to your list columns and date range.

<Query>
  <Where>
    <And>
      <Geq>
        <FieldRef Name="StartDate" />
          <Value IncludeTimeValue="TRUE" Type="DateTime">2011-01-01T15:55:52Z</Value>
      </Geq>
      <Leq>
        <FieldRef Name="StartDate" />
        <Value IncludeTimeValue="TRUE" Type="DateTime">2011-12-31T15:56:29Z</Value>
      </Leq>
    </And>
  </Where>
</Query>
  • Thanks Ryan. The concept of trying to think of a SQL query in CAML was just confusing me. Shortly after posting, I came up with exactly what you have, except for the <And>. Thanks – Tim Gabrhel Jul 7 '11 at 15:43
-4

Check this out:

http://social.msdn.microsoft.com/forums/en-US/sharepointdevelopment/thread/076c57b1-a780-4b86-b00b-849fa8789318

your two dates:

DateTime lastDay = new DateTime(DateTime.Now.Year, 12, 31);
DateTime dtThreeMonthsPrior = lastDay.AddMonths(-3);

Also, in your question you mentioned date like 9/31 which is invalid because 9th month has only 30 days.. lol

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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