Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

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

 

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi Anonymous ,

     

    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.

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago
    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
    • v-kkf-msft's avatar
      v-kkf-msft
      Community Support
      4 years ago

      Hi Anonymous ,

       

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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
  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    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