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.


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 +

Count the number of items returned in this Query.


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;
  • can u explain the above code – sherin Apr 6 '15 at 6:54
  • Sure. It first captures first day of current month and then captures first day of next month. Then finds number of listitems between these two dates which gives items of current month. – 404 Apr 6 '15 at 6:57
  • i need caml to fetch items in all months – sherin Apr 6 '15 at 6:59
  • Correct. So instead of using DateTime.Now.Year, you can pass year and month of your desired timeframe – 404 Apr 6 '15 at 7:01
  • how to count each item for a particular month and how to i set timeframe for all month – sherin Apr 6 '15 at 7:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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