DateTime format error, Copy Activity. Azure data factory

0
votes

I am copying data from CSV to Azure MySQL. I've a date field with format MM/DD/YYYY in source. It is throwing an error while copying data to MySQL. I tried changing the datatype in field mapping, pipeline ran succesfulbut data is not loaded. I need to convert the format to YYYY-MM-DD.

"errorCode": "2200", "message": "'Type=MySql.Data.MySqlClient.MySqlException,Message=Incorrect date value: '12/06/2010' for column 'xxx_dt' at row 49,Source=MySqlConnector,''Type=MySql.Data.MySqlClient.MySqlException,Message=Incorrect date value: '12/06/2010' for column 'XXX_DT' at row 49,Source=MySqlConnector,'", "failureType": "UserError",

Please suggect.

thanks.

2
azure-data-factory
Hi Geetha, If my answer is helpful for you, please accept it as answer( click on the check mark beside the answer to toggle it from greyed out to filled in.). This can be beneficial to other community members. Thank you. – Leon Yue

2 Answers

0
votes

Data Factory can not convert date format from 'MM/DD/YYYY' to 'YYYY-MM-DD' directly.

If you want convert the date format from 'MM/DD/YYYY' to 'YYYY-MM-DD', please using Data Flow with Derived Column.

For example, I have csv file with the column date format with "MM/DD/YYYY": enter image description here

I use Data Flow with bellow Derived Column expression:

toDate(concat(split({ born},'/')[3],'-',split({ born},'/')[1],'-',split({ born},'/')[2]))

enter image description here

The column born is convert to 'yyyy-mm-dd' format: enter image description here

You could follow my steps.

-1
votes

i guess you just need to convert it into any date format after reading from csv. If target is date.it wud accept any date format data