Date issue in ADF

Question

hi team,

I have azure adf pipeline where in the data is coming from excel. The date is in the format 'dd/MM/yyyy'. There are many date columns in the excel and there are many csv. When I am running the pipeline, it is giving error as String cannot be converted to valid DateTime. I searched about this and came to know about derived column wherein I am using logic "toDate(SnapshotDate1, 'dd/MM/yyyy', 'en-US')" here Snapshot*Date1 is the name of the column. But for this I have to map the column manually. I do not want to do that. Is there any logic to handle this such that it should run without me manually converting the date column from string to date?

could you please help me on this*

Answer 1

Hi @Dinesh Prajapati ,

Thankyou for your engagement with Microsoft Q&A platform .

As I understand your query, you want to apply the transformation rule in derived column dynamically on all date columns.

For that, you can use Column pattern as mentioned in this thread as well.

Kindly check the below documentation that can be helpful:

Using column patterns in mapping data flow

Kindly let me know if there is any thing I have missed to get in the query. I feel both queries are same. Please revert back with additional followup question if you have any. Otherwise, please accept the answer by clicking on Accept answer button and hit Yes for Was the answer helpful survey. Thankyou.