mapping Date in AZure Data Factory

Question

Im working with Data actory this time this why i ask lot of question about that

My new problem is that my SOURCE(CSV file contains a column DeleveryDate full of Date dd/MM/YYYY) and my table SQl where i specify DElevry date as DateTime but when I map btw source and sink in Data preview source

duplicate columns like in the picture below but in data preview sink the columns always NULL the same in my table NULL.

Thanks

Answer 1

You said column DeleveryDate full of Date dd/MM/YYYY), can you tell me why the column DeleveryDate has the values like '3', '1' in your screenshot? String '3' or '1' are not the date string with format dd/MM/YYYY.

If you want to do some data convert in Data Factory, I still suggest your to learn more about Data Flow.

For now, we can not convert date format from dd/MM/YYYY to datetime yyyy-MM-dd HH:mm:ss.SSS directly, we must do some other converts.

Look at bellow, I have a csv file contained a column with date format dd/MM/YYYY string, I still using DerivedColumn this time:

Add DerivedColumn:

Firstly, using this bellow expression to substring and convert dd/MM/YYYY to YYYY-MM-dd:

substring(Column_2, 7, 4)+'-'+substring(Column_2, 4, 2)+'-'+substring(Column_2, 1,2)

Then using toTimestamp() to convert it:

toTimestamp(substring(Column_2, 7, 4)+'-'+substring(Column_2, 4, 2)+'-'+substring(Column_2, 1,2), 'yyyy-MM-dd')

Sink settings and preview：

My Sink table column tt data type is datetime:

Execute the pipeline:

Check the data in sink table:

Hope this helps.

Answer 2

Please try this- This is a trick which was a blocker for me, but try this-

1. Go to sink
2. Mapping
3. Click on output format
4. Select the data format or time format you prefer to store the data into the sink.