3

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

  • is the internal name correct ? what is the column type of formatted date column ? – Gautam Sheth Jun 13 '17 at 8:55
  • 1
    Create a calculate field =TEXT([Date],"yyyy") and query the year from that field. – Nag1987 Jun 13 '17 at 9:30
4

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

0

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>

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.