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 comm...
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.

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 Anonymous ,

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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