Data Factory v2 add Query to Table Storage with function addmonths()

2
votes

We want to filter the data from a Table Storage since the first day of the last month using the utcnow() function.

For this we are applying a workaround in the first 5 days of the month using the following query: Timestamp ge datetime'@{formatDateTime(adddays(utcnow(),-5),'yyyy-MM-01')). However this implementation is limited to the trigger until a specific day of the month.

Is there any other way to use a function as addmonths(-1) for this query?

Thanks in advance!

1
azure-table-storageazure-data-factory-2

1 Answers

2
votes

addMonths and addYears are not supported by ADF so far which is mentioned in my previous case:Subtract number of days based on provided date parameter

Provide a trick for you,using substring and math function in dynamic content:

@formatDateTime(adddays(utcnow(),mul(add(int(substring(formatDateTime(utcnow(),'yyyy-MM-dd'),8,2)),1),-1)),'yyyy-MM-01')

For example, utcnow is 7/17/2019...., the result calculated by above expression is :

enter image description here

My idea is minus days+1 based on the now date. Please do a try.