Sp-dev-docs: 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:

day() 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.rnclientRequestId: xxxxxxxxxxxxxbrnserviceRequestId: 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?