CAML Query with Date Range

Question

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?

Answer 1

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>

Answer 2

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