How can i set "yesterday" in a variable? - Azure Data Factory

Question

I'm working on Azure Data Factory, and i'm looking to set one variable in my pipeline, that always represent the past day (yesterday). This is the solution that i found for the poor syntax integration via API with a CosmosDB that MS ADF has, so i cannot use CAST GETDATE -1 as i already used in other pipelines for SQL relational databases.

Already tried with all the possible solutions that i found on Microsoft Documentation:

Set the variable by the Activity "Set Variable", in order to pass through a parameter in my pipeline. Set the parameter with the formula directly

Formula: @addDays(utcnow(), -1)

I am having difficulties calling the variable / parameter in my "Copy Data" Activity, all the logic for the pipelines runs OK with parameters FromDate / ToDate. But when i replace it with a single parameter / variable : SELECT * FROM c WHERE c.Created = @variables('FechaAyer')

Gives me the following error:

"the function call must take the completion string"

I think i'm missing something

Answer 1

I tried your query in lookup and I got same error.

While using the ADF variables in queries, you need to use String interpolation or build the query using concat as suggested in comments.

In your pipeline you are using an array variable and comparing it with the values of cosmos. To achieve your requirement, use the string type variable.

Go through the below sample demo:

Here, I have taken a string type variable and given below dynamic content to get the yesterday date in yyyy-MM-dd format. You can give your date format.

@addDays(utcnow(), -1,'yyyy-MM-dd')

Now, I have used string interpolation in the query. For sample I have taken lookup.

select * from c where c.mydate='@{variables('forAyear')}'

Result: