REST API filter by month

Question

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.

Answer 1

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));
});

Answer 2

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