How to specify query for Azure Data Factory Source (Azure Table Storage) for yesterday's records

0
votes

I am copying records from an Azure Storage Table (source) to Azure Storage Table (sink) everyday. So if I am executing the query on December 24th 2019 (UTC) for instance, then I want to copy records for December 23rd 2019 (UTC). The query works and is doing what I intend it to do. Here is the query:

Timestamp ge datetime'2019-12-23T00:00Z' and Timestamp lt datetime'2019-12-24T00:00Z'

In the query above, the Timestamp column is automatically stamped in the Azure Storage Table when a new record is inserted in it. That is how Azure Storage Table works.

And here is the screenshot of the Data Factory Pipeline:

enter image description here

I want to parameterize the query now. That is: if the query is run on 24th December, 2019 then it should copy 23rd December 2019's records and keep sliding as it executes everyday on a schedule. I don't know how to do that. I know that there is a utcNow function and there is a subtractFromTime Function. I just don't know how to put it together.

2
azureparametersazure-table-storageazure-data-factory

2 Answers

0
votes

You can do something like this:

addDays(startOfDay(utcNow()), -1)

this would find the start of the previous day

https://docs.microsoft.com/en-us/azure/data-factory/control-flow-expression-language-functions#date-functions

3
votes

@4c74356b41, Thank you for your kind support. Based on your answers and some more googling, I was able to piece it together. Here is the final expression:

Timestamp ge @{concat('datetime','''',addDays(startOfDay(utcNow()), -1),'''')} and Timestamp lt @{concat('datetime','''',startOfDay(utcNow()),'''')}