azure-data-factory

I want to find all upcoming Fridays in the format `yyyyMMdd` using an Azure Data Factory expression


I need to get all Friday dates in the format yyyyMMdd so I can append them to file names in Azure Data Factory. I’ve tried the following expression. sample output 20241108, 20241115

@formatDateTime(addDays(utcnow(), (5 - dayOfWeek(utcnow()) + 7) % 7), 'yyyyMMdd')

Solution

  • To only get the next Friday, you need to use dynamic expressions like add(),sub() and mod() to perform the Arithmetic operations. Modify your expression like below.

    @formatdateTime(addDays(utcnow(),mod(add(7,sub(5,dayOfWeek(utcnow()))),7)),'yyyyMMdd')
    

    enter image description here

    Result:

    enter image description here

    If you want to get all upcoming Fridays, you should have an end date till when you need this list and you can use an until loop to get this list.

    First initialize create the following variables in the pipeline. In the Enddate, give your end date in yyyy-MM-dd format.

    enter image description here

    Initialize the counter variable with the first friday using the below expression in a set variable activity.

    @formatDatetime(addDays(utcnow(),mod(add(7,sub(5,dayOfWeek(utcnow()))),7)),'yyyy-MM-dd')
    

    Take the Until activity and give below expression as condition in the until activity.

    @greater(ticks(variables('counter')),ticks(variables('Enddate')))
    

    Inside until activity, take an append variable activity and append the counter variable to the friday_list variable. Give the below expression in the append variable activity.

    @formatDatetime(variables('counter'),'yyyyMMdd')
    

    enter image description here

    Next, increment the counter by 7 days. In ADF, self-referencing the variables is not supported currently. So, use a temp variable to store the incremented result with following expression in a set variable activity.

    @adddays(variables('counter'),7)
    

    Now, re-assign the temp variable to the counter variable with another set variable activity.

    In first iteration, the first Friday will be appended to the array and from then on, the counter will be incremented by 7 days and checked whether the next Friday is greater than the end date or not. If not, it will be appended in the next iteration. This will stop till the next Friday is greater than the given End date.

    At the end of the Until loop, the required Friday dates list will be stored in the friday_list variable which you can use later on as per your requirement.

    Result:

    enter image description here