question

AbhishekVyas-4546 avatar image
0 Votes"
AbhishekVyas-4546 asked ·

Azure Data Factory: Date Conversion


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.


azure-data-factory
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

HimanshuSinha-MSFT avatar image
4 Votes"
HimanshuSinha-MSFT answered ·

Hello @AbhishekVyas-4546

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 .

34358-dateissue.gif




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



dateissue.gif (1.1 MiB)
· 3 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I am creating copy activity to load data from txt file to Synapse SQL Dedicated Pool. In the file date string is "19450505" and I need to map this column to date type in SQL table.
I wrote following code but still I am getting following error message "Error converting data type VARCHAR to DATETIME", please help.

             "source": {
                     "name": "dob",
                     "type": "Datetime"
                     "format": "yyyyMMdd"
                 },
                 "sink": {
                     "name": "dob",
                     "type": "DateTime"
                 }
0 Votes 0 ·

@SudhakarMani-8683 : Can you please open a new thread for the ask please ? The initial ask was on data flow and I think your ask is more on the ADF side of the things .
Thanks
Himanshu

0 Votes 0 ·