How to write month value as a number from 1-12 using dynamic content functions when copying data in ADF?

Question

I'm trying to save copied data to my ADLS using the right folder path: <Subject Area>/<Data Source>/<Object>/y=<year>/m=<month>/d=<day>

Then I used the following expression when adding dynamic content to directory path to sink in copy activity and I'm getting the wrong month values according to documentation as follows:

@concat('<directory-path>/','y=',formatDateTime(<local time zone>,'yyyy'),'/','m=',formatDateTime(<local time zone>,'M'),'/','d=',formatDateTime(<local time zone>,'dd'))  

Documentation found about custom patterns - Custom date and time format strings

]3

"M" The month, from 1 through 12.

More information: The "M" Custom Format Specifier.

When I hit debug in ADF I'm getting the following in ADLS:

I'd like to have "m=1" as the folder name not , not "January 18" when using formatDatetime(<local time zone>,'M') as in the documentation is refered.

I'm not sure if it's because documentation is not up-to-date (2017) or if it's a bug. Can somebody check if I'm doing something wrong?

Thanks in advance for the support and help.

Answer 1

Hi @Felipe Regis E Silva ,

Thanks for your query and using this forum for raising your concern.

To get the desired output (to write folder name as a number from 1 to 12`) you can use the following dynamic expression:

   @concat('directory-path/','y=',formatDateTime(utcnow(),'yyyy'),'/','m=',if(equals(string(startswith(string(formatDateTime(utcnow(), 'MM')), '0')), 'True'), substring(formatDateTime(utcnow(), 'MM'),1,1), formatDateTime(utcnow(), 'MM')),'/','d=',formatDateTime(utcnow(),'dd'))  

I have tested this with a set variable activity and is working as expected. Please see below GIF

Here is the pipeline JSON payload, incase if you would like to play around with formatting.

   {  
       "name": "pipelineDateTimeConversion",  
       "properties": {  
           "activities": [  
               {  
                   "name": "setVarMonthStartingZero",  
                   "description": "Convert month numbers from (01-12) to (1-12)",  
                   "type": "SetVariable",  
                   "dependsOn": [],  
                   "userProperties": [],  
                   "typeProperties": {  
                       "variableName": "varMonth",  
                       "value": {  
                           "value": "@concat('directory-path/','y=',formatDateTime(utcnow(),'yyyy'),'/','m=',if(equals(string(startswith(string(formatDateTime(utcnow(), 'MM')), '0')), 'True'), substring(formatDateTime(utcnow(), 'MM'),1,1), formatDateTime(utcnow(), 'MM')),'/','d=',formatDateTime(utcnow(),'dd'))",  
                           "type": "Expression"  
                       }  
                   }  
               }  
           ],  
           "variables": {  
               "varMonth": {  
                   "type": "String"  
               }  
           },  
           "annotations": []  
       }  
   }  

I'm reaching out to ADF product team to confirm if this is the expected behavior in ADF or if it is a bug and will get back to you as soon as I have an update.

In the meantime, the above solution should help you to achieve your requirement.

Hope this helps and let us know if you have further query.

----------

Thank you
Please do consider to click on "Accept Answer" and "Upvote" on the post that helps you, as it can be beneficial to other community members.

Answer 2

After having conversation with internal team and re-visiting the document, you can use below format to write month numbers as 1-12. Please use this dynamic expression '%M' - Expression : formatDateTime(<local time zone>,'%M') which would further simplify your dynamic expression. This usage is being called out in this section of the document.

   @concat('<directory-path>/','y=',formatDateTime(<local time zone>,'yyyy'),'/','m=',formatDateTime(<local time zone>,'%M'),'/','d=',formatDateTime(<local time zone>,'dd'))  

Additional info: For usage of "M" custom format specifier please refer to this section of this doc

Here are the examples of "M" custom format specifier:

Hope this clarifies. Let us know if you have any further query.

----------

Thank you
Please do consider to click on "Accept Answer" and "Upvote" on the post that helps you, as it can be beneficial to other community members.