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.

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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 🙂