CODEWITHSUNDEEP
excelazurecsvazure-data-factory
Brian
Brian

Reputation: 169

Opening CSV in Excel not displaying Datetime2 correctly

I am building an ETL solution in Azure Data Factory that calls a stored proc and populates a CSV

The date columns are defined as datetime2 in the source and is being mapped to datetime in the CSV:

enter image description here

When I open the CSV in Excel, the dates are displayed as 00:00.0 with the formula bar showing 09/04/2019 00:00:00:

Risk_Inception_Date Risk_Expiry_Date
00:00.0 00:00.0
00:00.0 00:00.0
00:00.0 00:00.0
00:00.0 00:00.0
00:00.0 00:00.0
00:00.0 00:00.0
00:00.0 00:00.0
00:00.0 00:00.0

If I save the CSV in Excel and open it in Notepad++ the dates change to 00:00.0

enter image description here

This is causing me a massive headache and can't find a way around it.

Any ideas?

Thanks

Brian

EDIT1: As per Marc's suggestion, map all the datetime2 source fields to plain text in the ADF control flows?

enter image description here

Upvotes: 0

Views: 661

Answers (1)

NiharikaMoola
NiharikaMoola

Reputation: 5074

You can convert the source Date to the required format under Mapping in Type Conversion Settings.

You can add your custom date format for conversion in dateTimeFormat or dateTimeOffsetFormat for example, MM/dd/yyyy HH:mm:ss:fff

enter image description here

Output before conversion:

enter image description here

Output after adding type conversion in ADF mapping:

enter image description here

Upvotes: 1

Related Questions