Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 8 down vote accepted

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>
share|improve this answer
    
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

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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