ADF - Change the date format from any format coming from csv to yyyy-MM-dd HH:mm:ss while loading in target sql table taking datetime

Question

I have a situation where I am getting dates in two separate formats, MM/dd/yyyy & yyyy-dd-MM, AND there might be even more different formats as well in csv which will be obviously in string.

Below are the data which currently come as String from CSV-

1/14/2022 0:00
2021-12-31 00:00:00

I am using a Dataflow task in ADF to load the data into Azure SQL where the default format it uses should be yyyy-MM-dd HH:mm:ss.

how can I do this?

Answer 1

ok, i managed to build a quick demo.

Main idea of my solution:

you need to differentiate between valid rows and rows that needs to be modified. in order to do so, i used case condition. the idea is to add a derived column with a name 'Date' and modify only needed rows.

Input Data:

i created a csv file and saved my data as a dataset in ADF.

ADF:

In source, i select my dataset as an input.

in a derived column activity:

added a new derived column with a name 'Date' , value :

case(contains(split(Date,''),#item=='/'), toString(toTimestamp(Date,'MM/dd/yyyy H:mm'),'yyyy-MM-dd HH:mm:SS'), Date)

in toTimestamp method, i added first the dateFormat of my input Date and in toString the desired format that i want to cast the date to it.

Output:

P.s You can cast all possible date formats that will appear in your data in that way.

you can read more about it here: https://learn.microsoft.com/en-us/azure/data-factory/data-flow-expressions-usage#toTimestamp