Azure Data Factory automatically adding Date to a Time column while importing an Excel file

Question

I have a time column in Excel that contains values of Time Type in the following format:

03:11:00

When I add the Excel File as a Source in ADF, it automatically converts it to DateTime and when I preview the data I see this:

1899-12-31 03:11:00.0000000

What can I do to prevent this conversion from happening?

Answer 1

When you add an Excel file as a source in ADF, it automatically converts the time column to DateTime in data preview. To convert, you can use the Data Flow activity in ADF and use the Derived Column transformation to convert the DateTime column back to the original time format. You can use the substring function to extract the time part of the timestamp. Here is an example expression that you can use in the Derived Column transformation:

substring(<DateTimeColumn>, 12, 8)

Replace <DateTimeColumn> with the name of your DateTime column. This expression will extract the time part of the DateTime column in the format hh:mm:ss.