how to write caml query to count item created in each month

Question

I need to write CAML query to count items created in each month.It should give me a count of items created in particular month.

Answer 1

Use SPUtility.CreateISO8601DateTimeFromSystemDateTime to create relevant DateTime string.

DateTime firstDay = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
string firstDayValue = SPUtility.CreateISO8601DateTimeFromSystemDateTime(firstDay);
string firstDayValueplus1Month = SPUtility.CreateISO8601DateTimeFromSystemDateTime(firstDay.AddMonths(1));
SPQuery query = new SPQuery();
query.Query = "<Where><And><Geq><FieldRef Name='Date' /><Value  Type='DateTime'>" + firstDayValue +
                    "</Value></Geq><Leq><FieldRef Name='Date' /><Value Type='DateTime'>" + firstDayValueplus1Month +
                    "</Value></Leq></And></Where>";

Count the number of items returned in this Query.

Example:

using (SPSite site = new SPSite(properties.Web.Site.ID))
{
using (SPWeb web = site.OpenWeb(properties.Web.ID))
{ 
SPList list = web.Lists["ListName"];
SPListItemCollection listItems = list.GetItems(query);
int itemCount = listItems.Count;
}
}