As per given document Odata filter on datetime not working with day, month, year etc function.

[anomepani]

I am trying filter records based on datetime Odata filter on year function but not working. I have tested with below function

Date and time OData functions:

month()
year()
hour()
minute()
second()

does not seem to work with SharePoint Online REST Service

Error: The query is not valid

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 916d6284-9149-5d95-48d8-40c60ba9f3c7
• Version Independent ID: 9c41a103-56d6-c186-26aa-ccd098a61f7e
• Content: Use OData query operations in SharePoint REST requests
• Content Source: docs/sp-add-ins/use-odata-query-operations-in-sharepoint-rest-requests.md
• Product: sharepoint
• GitHub Login: @spdevdocs
• Microsoft Alias: spdevdocs

[msft-github-bot]

Thank you for reporting this issue. We will be triaging your incoming issue as soon as possible.

[andrewconnell]

Please provide some context... such as an example query. For example, have you verified the query is a valid OData query?

[msft-github-bot]

The more context details you can provide, the easier it is to help assist on issues. Any code you can provide and/or screenshots of the issue also help. The easier you can make it to reproduce the issue, the easier and quicker it is for someone to help you. Please refer to How to Create Good Issues, specifically How to Create Good Issues: Include context, in our wiki for more details.

[anomepani]

I am using query like this, https://brsupport. Sharepoint.com/_api/Lists/GetByTitle('test')/items?$filter=year(salesdate) eq 2019
I am not sure where I am doing wrong, I am using below type filter.
$filter=year(salesdate) eq 2019
Tried with wrapping value in string as well
$filter=month(salesdate) eq '2019'

Hope this information will helpful

[Jerry0527]

Please refer the same question in StackOverflow:
REST API filter by month

Use Listdata.svc:
/_vti_bin/listdata.svc/ListName?$filter=year(columnName) eq YearNumber

Example:
/_vti_bin/listdata.svc/test?$filter=year(salesdate) eq 2020

[anomepani]

I have also tried same but for sharepoint online it's not working, I have Reffered this answer but as it's not working I have rised queries on github.

Thank for reply

[Jerry0527]

@anomepani, Listdata.svc is still available in SharePoint Online.
What's the response in your side ? Is there any error status.
You can also do a Post Request with CAML same as the Option 2 in the similar thread above.

[andrewconnell]

The primary OData endpoint, _api, should respect these functions per the docs. You should not be reverting back to the legacy listdata.svc endpoint or having to revert to CAML.

I can repro your error.

Investigating & will follow up...

[bcameron1231]

fwiw, I've never seen date functions work in the REST API, only in the SOAP APIs.

[diveshgup]

Hello,

I am trying to filter on a datetime field and i am getting issues.

When i use rest api and filter on Created, it works. however when i use EventDate, it does not.
When i use listdata.svc, same issue occurs.

Thanks.
Divesh

[thailakadre]

Hi, I am trying to use the OData function too in power automation(Flow). And I am receiving the same error.

Error:
body":{"status":400,"message":"The query is not valid.\r\nclientRequestId: xxxxxxxxxxxxxb\r\nserviceRequestId: xxxxxx"}}

Filter query.
day(Birthday) eq 28 and month(Birthday) eq 10

Please, someone knows if I am doing something wrong or if this error still happens?

[ghost]

This issue is being closed as part of an issue list cleanup project. Issues with no activity in the past 6 months that aren't tracked by engineering as bugs were closed as part of this inititive. If this is still an issue, please follow the steps outlined to re-open or submit a new issue.

[ghost]

Issues that have been closed & had no follow-up activity for at least 7 days are automatically locked. Please refer to our wiki for more details, including how to remediate this action if you feel this was done prematurely or in error: Issue List: Our approach to locked issues