Converting MMM DD YYYY HH:mm:ss datetime format to dd-mm-yyyy HH:mm:ss in Azure DataFlow

Question

How to convert date time formats like Jun 18 2023 8:55:31 PM to 18-06-2023 20:55:31 dd-mm-yyyy HH:mm:ss as a Derived Column step in ADF Dataflow?

Answer 1

As per this Documentation, the above date format is not supported in ADF dataflow expressions. I have tried to convert the above MMM dd yyyy format to required format with the dataflow expressions but got null values.

So, as a workaround you can try to convert those to required format using string operations. For this, you need to take your source date column as string column in the projection.

First create an array parameter in dataflow with below values(Month in MMM-month in MM).

["Jan-01","Feb-02","Mar-03","Apr-04","May-05","Jun-06","Jul-07","Aug-08","SeptSep-09","Oct-10","Nov-11","Dec-12"]

Use the below expression in derived column transformation to generate the output.

concat(split(date,' ')[2], '-',split(find($arr,instr(#item,split(date,' ')[1])!=0),'-')[2],'-',split(date,' ')[3],' ',iif(instr(date,'PM')==0,split(date,' ')[4],replace(split(date,' ')[4],split(split(date,' ')[4],':')[1],toString(add(12,toInteger(split(split(date,' ')[4],':')[1]))))))

Example: date is Jun 18 2023 8:55:31 PM.

• First we need is date, So I am getting 2nd item if split of input column with space ' ' i,e. 18.
• For month, I am getting the MMM values by split and comparing the value with the array values to get the MM values.
• Then add the year. For the time, if it contains 'PM', add 12 to the hour and replace the hour in the original string. If it does not contains 'PM', then add the same string.

Result:

(OR)

If you want to achieve your requirement without the array, you can try this:

concat(substring(split(toString(toTimestamp(date, 'MMM dd yyyy hh:mm:ss a')),'-')[3],1,2),dropLeft(replace(toString(toTimestamp(date, 'MMM dd yyyy hh:mm:ss a')),substring(split(toString(toTimestamp(date, 'MMM dd yyyy hh:mm:ss a')),'-')[3],1,2),split(toString(toTimestamp(date, 'MMM dd yyyy hh:mm:ss a')),'-')[1]),4))