Azure Datafactory or Synapse, Dynamic Date Question

Question

I'm looking to copy data from s3 to azure data lake. Every week the provider creates a folder that has the date of the most recent sunday. For instance:

pipeline_data/2021_08_15/File

I'm looking to find a way to dynamically write the 2021_08_15 part. I can do it in python easily but can't figure it out with Microsoft expression language. Any help would be greatly appreciated.

Answer 1

Hi @B K ,

Welcome to Microsoft Q&A Platform. Thank you for posting your query.

To meet your requirement, you could try the below dynamic content :

@formatDateTime(utcnow(), 'yyyy_MM_dd')  

It gets the current time and converts to the format 'yyyy_MM_dd' (Year,Month,Date)

Output :

Note :

The above dynamic content makes use of the UTC time - if you would like a different time zone you could make use of the function ConvertTimeZone

@formatDateTime(convertTimeZone(utcnow(),'UTC','Pacific Standard Time'), 'yyyy_MM_dd')  

-------------------

Update 1

You would like to create a folder with the Last Sunday from today's date

Without the time zone

 @formatDateTime(adddays(utcnow(),sub(0,mod(dayOfWeek(utcnow()),7))), 'yyyy_MM_dd')  

With Time zone

@formatDateTime(convertTimeZone(adddays(utcnow(),sub(0,mod(dayOfWeek(utcnow()),7))),'UTC','Pacific Standard Time'), 'yyyy_MM_dd')  

You could try the above snippets.

**Code Explanation: **

1. Gets the current weekday number starting from Monday
2. Does mod division with 7 and subtracts the resultant from today's date.
3. Applies the necessary formatting on the resultant date as per initial answer

For instance - let's take 30th August - Monday- Week Day number is 1

1%7 = 1.
1 day is subtracted from today (30th August) which 29th August (=Sunday)
Necessary formatting is done on this date (=29_08_2021)

Hope this helps. Do let us know if you any further queries.

------------

• Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.
• Want a reminder to come back and check responses? Here is how to subscribe to a notification.

Answer 2

for some reason when I run this now I get the following error:

@formatDateTime(convertTimeZone(addDays(utcNow(),sub(0,mod(dayOfWeek(utcNow()),7))),'UTC','Eastern Standard Time'), 'yyyy_MM_dd')

The parameters and expression cannot be resolved for schema operations. Error Message: { "message": "ErrorCode=InvalidTemplate, ErrorMessage=In the function 'convertTimeZone', the value provided for the time zone id 'Eastern Standard Time' was not valid. " } Activity ID: d2157ab3-220a-4a41-b2c8-ec0438238b8d

Any help would be greatly appreciated. Worked fine for months.