Error message when using dynamic date in ODATA filter - maybe because I'm in Europe?

[Syndicate_Admin]

Can't figure out the issue here, but it may have something to do with different rules regarding using one or two citation marks between US and EU. I am in Europe, and I am getting an "expected a comma here" error when using this line of code, to get some ODATA results:

OData.Feed("https://api.url/Report?$filter=Posting_Date ge " & Date.ToText(Date.AddMonths(Date.From(DateTime.LocalNow()),-6),'yyyy-MM-dd')) "
, null, [Implementation="2.0"])

Can someone help? Thanks

[Syndicate_Admin]

OData.Feed("https://api.businesscentral.dynamics.com/v2.0/101eb68a-ed35-41e3-93fa-103c567c18e1/Production/ODataV4/Company('My%20Company')/Finansposter_Excel?$filter=Posting_Date ge " & Date.ToText(Date.AddMonths(Date.From(DateTime.LocalNow()),-6),'yyyy-MM-dd')) "

[Syndicate_Admin]

Hi @phkGZ ,

Please try the following code.

let
    Source = OData.Feed("https://api.url/Report?$filter=Posting_Date ge '" & Date.ToText(Date.AddMonths(Date.From(DateTime.LocalNow()),-6),"yyyy-MM-dd") & "'" 
    , null, [Implementation="2.0"])
in
    Source

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Hi @phkGZ ,

Has your problem been solved? If it is solved, please mark a reply which is helpful to you.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[Syndicate_Admin]

Hi @phkGZ ,

Has your problem been solved? If it is solved, please mark a reply which is helpful to you.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[Syndicate_Admin]

Hi Winniz, sorry for not replying, was on a quick vacation.

Tried your solution and I got a step further, now its complaining about datatypes. The way I read it is that I cannot use "greater than or equal" with PostingDate, is that correct?

DataSource.Error: Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed: 
OData Version: 3 and 4, Error: The remote server returned an error: (400) Bad Request. (A binary operator with incompatible types was detected. Found operand types 'Edm.Date' and 'Edm.String' for operator kind 'GreaterThanOrEqual'.  CorrelationId:  5e77805e-4973-4b5b-af16-c9323a6ef884.)
OData Version: 4, Error: The remote server returned an error: (400) Bad Request. (A binary operator with incompatible types was detected. Found operand types 'Edm.Date' and 'Edm.String' for operator kind 'GreaterThanOrEqual'.  CorrelationId:  05233492-e120-40f7-99e8-023b747d8734.)
OData Version: 3, Error: The remote server returned an error: (404) Not Found. (Not Found)
Details:
    DataSourceKind=OData

[Syndicate_Admin]

Hi @phkGZ ,

Take uniconta api as an example, DateTime values must be delimited by single quotation marks and preceded by the word datetime, such as 

let
    Source = OData.Feed("https://odata.uniconta.com/odata/GLTransSumClient?$filter=Date ge datetime'" & Date.ToText(Date.AddMonths(Date.From(DateTime.LocalNow()),-6),"yyyy-MM-dd") & "T00:00:00'" , null, [Implementation="2.0"])
in
    Source

 If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Hi @v-kkf-msft 

Thanks for coming back to this! I copy pasted your code to make sure no typing errors snuck in, and got a new error this time, so I think we are close now!

This is the error I get:

DataSource.Error: Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed: 
OData Version: 3 and 4, Error: The remote server returned an error: (400) Bad Request. (Unrecognized 'Edm.String' literal 'datetime'2022-01-07T00:00:00'' at '16' in 'Posting_Date ge datetime'2022-01-07T00:00:00''.  CorrelationId:  3e03fbab-a1e1-47af-976f-9a13c6779d0b.)
OData Version: 4, Error: The remote server returned an error: (400) Bad Request. (Unrecognized 'Edm.String' literal 'datetime'2022-01-07T00:00:00'' at '16' in 'Posting_Date ge datetime'2022-01-07T00:00:00''.  CorrelationId:  b6bcc1c8-5cde-4e18-9c89-00997007477a.)
OData Version: 3, Error: The remote server returned an error: (404) Not Found. (Not Found)
Details:
    DataSourceKind=OData
    DataSourcePath=xxxxx/Finansposter_Excel

[Syndicate_Admin]

Hi @phkGZ ,

Please set ODATA version to 4, and then use DateTimeOffset to query.

let
    Source = OData.Feed("http://services.odata.org/V4/northwind/northwind.svc/Orders?$filter=OrderDate ge " & Date.ToText(Date.AddMonths(Date.From(DateTime.LocalNow()),-300), "yyyy-MM-ddT23:59:59.99Z"),  null, [ODataVersion=4])
in
    Source

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Hi winniz!

Great, it definitely seems to work now - the code computes without error, and I can pull the data.

However I can't figure out the parameter you put in - you put in the number 300. If I use that, it selects all data I have in my source. I want to try and select the last 6 months, or there about.

If I put in "0" as a parameter, it takes today and forward in time.

If I put in "-1" as a parameter, it takes yesterday, and forward in time.

So that makes sense. So I figured if I put in "-10", it's going to take the last 10 days, and forward in time. But no - then it selects all data in my source, again.

HAH! Just writing this, I figured it out. "-2" gave me the last two months. So then "-6" gave me the desired result - from january 7th and forward.

AWESOME, thanks again 🙂