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(@"
        <FieldRef Name='Record_x0020_Yr_x0020_End_x0020_Date' />
        <Value Type='DateTime' IncludeTimeValue='FALSE'>{0}</Value>
</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?

2 Answers 2


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.

        <FieldRef Name="StartDate" />
          <Value IncludeTimeValue="TRUE" Type="DateTime">2011-01-01T15:55:52Z</Value>
        <FieldRef Name="StartDate" />
        <Value IncludeTimeValue="TRUE" Type="DateTime">2011-12-31T15:56:29Z</Value>
  • 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 Commented Jul 7, 2011 at 15:43
  • Keep in mind that columns included in your <Where> will probably need to be an indexed column within Sharepoint in order for the query to work, especially if your record count is over the ListView threshold.
    – Corgalore
    Commented Aug 11, 2022 at 15:18

Check this out:


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 and acknowledge you have read our privacy policy.

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