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?

Ryan · Accepted Answer · 2011-07-07 14:59:13Z

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
– Tim Gabrhel, 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
– Corgalore, Commented Aug 11, 2022 at 15:18

Ashish Patel · Accepted Answer · 2011-07-07 14:53:00Z

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

Stack Exchange Network

CAML Query with Date Range

2 Answers 2

Linked

Hot Network Questions

CAML Query with Date Range

2 Answers 2

Linked

Related

Hot Network Questions