5

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.

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

2 Answers 2

10

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));
});
2
  • 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, 2014 at 12:57
  • 2
    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 Nov 26, 2014 at 13:03
1

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 and acknowledge you have read our privacy policy.

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