Forum Discussion

JayJay11's avatar
JayJay11
Resolver II
2 years ago
Solved

Problem with variables and dynamic API query

Hello all,   so I am trying to use OData with a specific URL to retrieve data from SAP HANA. The URL string that I want to send looks as the following:   $select=CompanyCode,AccountingDocument,Le...
Pipelines
  • JayJay11's avatar
    JayJay11
    2 years ago

    Again, this is not producing what I want, as it is adding things at the end of the string.

     

    I solved it like that:

     

    @concat('&$filter=PostingDate gt datetime''',substring(variables('vReferenceDateTime'), 0, 10), 'T00:00:00''')
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi JayJay11 ,

Thanks for using Fabric Community.

Can you please try this -

@formatDateTime(utcnow(),'yyyy-MM-ddTHH:mm:ss zzz')

 

Output
{ "name": "Change Date format", "value": "2024-01-31T22:26:37 +00:00" }

Hope this is helpful. Please let me know incase of further queries.
JayJay11's avatar
JayJay11
Resolver II
2 years ago

Hi Anonymous thank you for supporting. Though I am not sure how this is solving it, as this is again not the desired format. But let's nevermind that for a moment, I have a general problem understanding how variables work.

 

I try the following:

 

So with the first activity, I create a string. That string I store in a variable, which I then want to use in my copy action as following:

 

 

This pipeline should only run about 20 seconds, which is the case if I put the string directly to the Query without using a variable. However, when I use a variable, the pipeline runs much much longer, I had to stop it after 15 minutes.

 

What am I doing wrong here?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi JayJay11 ,

    I have tried both the ways using Set Variable and normal but didn't find any difference in exceution.

    With Set Variable:


    Without Set Variable:


    Inorder to debug please check the output of Set Variable -

    Can you please check your syntax?

    Correct Syntax:

    @concat('sales__1_test','1')

     

    • JayJay11's avatar
      JayJay11
      Resolver II
      2 years ago

      Anonymous thank you - actually adding the "@" solved it. Great!

       

      Now only the problem with the datetime. How can I get exactly the format "2024-02-21T00:00:00" as a string starting from the function utcnow()? The pipeline always adds this "Z" at the end, why?

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi JayJay11 ,

         

        @formatDateTime(utcnow(),'yyyy-MM-ddTHH:mm:ss zzz')

         



         


        Can you check this format and let me know if that works?