Question Regarding Azure Data Factory Date Field Data type

Question

Hello,

I want to ask if anyone has a solution to how to allow a date field datatype to export to Azure Storage Explorer through an Azure Data Factory process with the same date data type as from Azure SQL Database table ((note: the data type for date field in the SQL table is datetime2(0)). In the SQL database table, the date does not include the milliseconds but in the Azure Storage Explorer .txt file it has the milliseconds.

Example of how the date appears in the SQL table

NameDateSue2023-01-31 00:00:00Bob2023-02-28 00:00:00An example of how the data appears in Azure Storage Explorer (as a .txt file) once table above ran through the azure data factory process

Name|Date

"Sue"|2023-01-31 00:00:00.0000000

"Bob"|2023-02-28 00:00:00.0000000

Answer 1

You can use a derived column transformation to modify the format of your datetime field.

The expressioncan be something like toTimestamp([YourDateColumn], 'yyyy-MM-dd HH:mm:ss') to convert the datetime to a timestamp while specifying the format without milliseconds.

Answer 2

Hi DaeDae89,

Thank you for posting query in Microsoft Q&A Platform.

If you are using copy activity here, then kindly try working with type conversion settings and that should help you to convert dates as per the required formats. If you are working with dataflows, then use derived column transformation to convert the date formats.

Hope this helps. Please let me know how it goes or if any further queries.

---

Please consider hitting Accept Answer button. Accepted answers help community as well. Thank you.