3

I would like to filter a datetime field but I only want to filter MONTH

var requestUri = "xxxx/getByTitle('Contact')/items?$select=CellPhone,mdate,ddate&$filter= Birthday ge '" + today + "'";

The query is working fine and I also tried month(birthday) as it was in the odata specifications but didn't work.

How can I get the month from the Birthday Datetime field.I can get it from my today variable.

  • Are you asking how to filter items by a given month, or how to get items and display only month? – eirikb Nov 25 '14 at 7:20
9

Date and time OData functions:

  • day()
  • month()
  • year()
  • hour()
  • minute()
  • second()

does not seem to work with SharePoint 2013 REST Service

Error: The query is not valid

But you could consider the following approaches.

Option 1

Utilize SharePoint 2010 REST endpoint:

/_vti_bin/listdata.svc/ListName?$filter=month(<DateColumn>) eq <MonthNumber>

Example:

/_vti_bin/listdata.svc/Employees?$filter=month(Birthday) eq 9 

Option 2

  • Create a calculated column BirthdayMonth and specify the following formula:=MONTH(Birthday)
  • Use CAML query to filter list items by month as demonstrated below.

How to retrieve list items using CAML query:

function getListItems(webUrl,listTitle, query) 
{
    var url = webUrl + "/_api/web/lists/getbytitle('" + listTitle + "')/getitems"; 
    var queryPayload = {  
               'query' : {
                      '__metadata': { 'type': 'SP.CamlQuery' }, 
                      'ViewXml' : query  
               }
    };

    return $.ajax({
           url: url,
           method: "POST",
           data: JSON.stringify(queryPayload),
           headers: {
              "X-RequestDigest": $("#__REQUESTDIGEST").val(),
              "Accept": "application/json; odata=verbose",
              "content-type": "application/json; odata=verbose"
           }
     });
}

Usage

var today = new Date();
var monthKey = today.getMonth();
var query = String.format('<View><Query><Where><Eq><FieldRef Name="BirthdayMonth" /><Value Type="Number">{0}</Value></Eq></Where></Query></View>',monthKey);
getListItems(_spPageContextInfo.webAbsoluteUrl,'Empployees',query)
.done(function(data)
{
     var items = data.d.results;
     for(var i = 0; i < items.length;i++) {
         console.log(items[i].Title);
     }    
})
.fail(
function(error){
    console.log(JSON.stringify(error));
});
  • 1
    Thanks Vadim for the reply will give this a shot. So it seems $filter=month(<DateColumn>) eq <MonthNumber> doesn't work with SharePoint 2013? – naijacoder Nov 26 '14 at 12:57
  • 1
    That's right, for example SharePoint 2013 REST endpoint /_api/web/lists/getByTitle('Employees')/items?$filter=month(Birthday) eq 9 just returns the error: The query is not valid. It seems there is a issue related implementation of Date and Time OData functions in SharePoint 2013 REST API – Vadim Gremyachev Nov 26 '14 at 13:03
0

You can create column with 'date only' and then you can pass date directly in sharepoint format.

_api/web/lists/getByTitle('ListName')/items?$select=Id,Title,Modified,Birthdate&$filter=Birthdate eq '2016-04-13'

Its working fine for me (Tested on SharePoint online).

For 'Date and time' Field we have to format using javascript in order pass in SharePoint RestAPI

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.