Wrong datetime format detection in Azure Data Factory Data Flows

Christian Føsund 1 Reputation point
2022-05-03T09:34:21.793+00:00

Hi,

I'm trying to read data correctly from Dynamics CRM through the Azure Synapse Link for Dataverse with the inline "Common Data Model" connector in Azure Data Factory Data Flows. I'm able to connect to the source using the Model.json and selecting the correct entity.

But the projection for some of the datetime columns are wrong. Specifically, there are column named “SinkCreatedOn” and “SinkModifiedOn” where it’s not able to detect the correct format.

In the source files in the cdm folder in the storage account the correct value is: “3/2/2022 12:19:12 PM” but the data preview shows: “2022-03-02 12:19:03.000”, missing the AM /PM.

198522-image.png

I have tried to use different formats in the schema options for the projection, but none of them matches.

I have also tried to add custom timeformats in the script file. but still unsuccessful.
timestampFormats: ['MM/dd/yyyy hh:mm:ss a','M/d/yyyy hh:mm:ss a']) ~> source1

How can I configure my ADF Data Flow with the CDM Inline connector to detect the datetime format correctly for the value: “3/2/2022 12:19:12 PM”

Thanks!

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,655 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,083 questions
{count} votes

1 answer

Sort by: Most helpful
  1. MartinJaffer-MSFT 26,056 Reputation points
    2022-05-06T18:10:38.867+00:00

    I have some findings to explain @Anonymous

    So my sample data is a CSV, note the AM and PM both starting with 12:19 :

    1,3/2/2022 12:19:12 PM  
    2,1/1/2023 11:11:11 AM  
    3,4/2/2022 12:19:12 AM  
    

    with timestamp format

    M/d/yyyy hh:mm:ss a  
    

    I previewed results:

    1	2022-03-02 12:19:12.000  
    2	2023-01-01 11:11:11.000  
    3	2022-04-02 00:19:12.000  
    

    199831-image.png

    What we see here, is Dataflow not ignoring AM/PM, but instead converting it to military time / 24 hour instead of 12 hour.