Azure Data Factory: Date Conversion

Question

I am creating a pipeline where the source is csv files and sink is SQL Server.

The date column in CSV file may have following values like

12/31/2020
10162018
20201017
31/12/1982
1982/12/31

I do not find the function which checks the format of the date.
How do I check the format and convert the above values to 'yyyy-MM-dd' format.

Answer 1

Hello @Abhishek Vyas

Thanks for the ask and also using the Microsoft Q&A.
I was able to get this solution working using mapping dataflow(MDF).
We need to use the derived column , todate and coalesce function.

coalesce(toDate(Somedate,'MM/dd/yyyy'),toDate(Somedate,'yyyy/MM/dd'),toDate(Somedate,'dd/MM/yyyy'),toDate(Somedate,'MMddyyyy'),toDate(Somedate,'yyyyddMM'),toDate(Somedate,'MMddyyyy'),toDate(Somedate,'yyyyMMdd'))  

Please find the attached below to get the implementation . Please do let me know how it goes .

Thanks Himanshu
Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

Answer 2

Hi Team,

How to convert epoch timestamp to datetime in Azure data factory
suppose i have values like below
epocdatetime
1626190402
1626190400
Please help