azure-data-factorydate-formatderived-column

How to check date format in Azure Data Factory

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

The date column in CSV file may have 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.

Solution

  • The solution is given by HimanshuSinha-msft

    Solved the issues using expression builder in Derived Column in Mapping Data Flow.

    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'))