azureazure-data-factory

How to set date format to output date as "yyyy-MM-dd" in Azure Data Factory

I have an ADF data flow task to convert an excel file to CSV format. I want to create CSV file with "yyyy-MM-dd" date format.

I have set sink output format to "yyyy-MM-dd" but it does not work.

enter image description here

The source column projection has been set to "date" as well.

enter image description here

Sample Input File (XLS):

ID | Date

1 | 13/01/2022

Current Output File (CSV):

ID, Date

1, 13/01/2022

Expected Output File (CSV):

ID, Date

1, 2022-01-13

Solution

  • How to set date format to output date as "yyyy-MM-dd" in Azure Data Factory

    Don't provide the default date format in the sink settings to get the expected output. Below is the process where i don't provide any default date format in the sink. So, the output is in expected format as you can see below.

    Follow the below steps to get the expected output:

    Step1:
    Please try using the following expression without the default format to achieve the expected results: toDate(Date, 'dd/MM/yyyy', 'yyyy-dd-MM'). enter image description here

    Step2: Based on your input, I have used the same sample data that you provided. enter image description here Step3:
    I have provided the following expression in the derived column as required. Please use the following expression in the derived column as required: toDate(Date, 'dd/MM/yyyy', 'yyyy-dd-MM'). enter image description here Step4: I'm generating the output in CSV file format as per your requirements. enter image description here