Query the year from a date field in CAML Query

Question

I have a SharePoint 2013 list in which I have a date field say Formatted Date, now using CAML Query I need to retrieve only the records which have year 2017 in the Formatted Date.PFB my code:

$().SPServices({
    operation: "GetListItems",
    listName:"Test List",
    webURL:"http://ecf.pfizer.com/sites/Test/",
    CAMLViewFields: '<ViewFields><FieldRef Name=\Region\'/><FieldRef Name=\FormattedDate\'/></ViewFields>',     
    CAMLQuery:"<Query><Where><And><Eq><FieldRef Name=\Region\'><Value Type='Choice'>"+region[i]+"</Value><Contains><FieldRef Name=\FormattedDate\'><Value Type='DateTime'>2017</Value></Contains></Eq></And></Where></Query>",
    async: false,
    completefunc: function (xData, Status) 
    {
        year2016= parseInt($(xData.responseXML).SPFilterNode("rs:data").attr("ItemCount"));
    }

But the count is retrieved as 0. Can somebody help? Thanks in advance

Answer 1

Your ending </Eq> tag is misplaced. It should be placed before the <Contains> tag. Also some of your escapings are incorrect.

<Query>
   <Where>
       <And>
           <Eq>
               <FieldRef Name='Region'>
               <Value Type='Choice'>"+region[i]+"</Value>
           </Eq>
           <Contains>
               <FieldRef Name='FormattedDate'>
               <Value Type='DateTime'>2017</Value>
           </Contains>
        </And>
    </Where>
</Query>

Also, I'm not sure if the <Contains> will work on a date-time field. I normally try to use something like:

Greater than or equal to 2017-01-01 AND Less than 2018-01-01

Answer 2

Below is the correct query, please note use this date format for strLeaveStartDateFilter and strLeaveEndDateFilter string objects

string strLeaveStartDateFilter = "2017-01-01";
string strLeaveEndDateFilter  = "2018-01-01"

<Where>
    <And>
        <Eq>
                    <FieldRef Name='Region'></FieldRef>
                    <Value Type='Choice'>"+region[i]+"</Value>
            </Eq>
        <And>
                    <Geq>
                            <FieldRef Name='FormattedDate'></FieldRef>
                                <Value Type='DateTime' IncludeTimeValue='FALSE'>" + strLeaveStartDateFilter + @"</Value>
                        </Geq>
                        <Lt>
                            <FieldRef Name='FormattedDate'></FieldRef>
                                <Value Type='DateTime' IncludeTimeValue='FALSE'>" + strLeaveEndDateFilter + @"</Value>
                        </Lt>
                 </And>
    </And>
</Where>