Wrong datetime format detection in Azure Data Factory Data Flows

Question

Wrong datetime format detection in Azure Data Factory Data Flows

Christian Føsund 1

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.

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!

MartinJaffer-MSFT 26,236 Reputation points

2022-05-04T16:42:04.047+00:00

Hello and welcome to Microsoft Q&A @Anonymous .

If I understand correctly, the issue is that the AM/PM part of your timestamp isn't being read correctly into ADF Data Flow. The format presented isn't a problem.

I don't have a Dataverse environment set up, so I'll make some csv with your example date to test on. This is making assumption this is not Dataverse/CDM specific. Is this acceptable?
Ryan Abbey 1,186 Reputation points

2022-05-05T03:45:12.043+00:00

Are you sure it's not just using your locale settings and showing you the time as per 24 hour clock? Have you got some modifications that are after 1pm to confirm?

1 answer

Your answer

MartinJaffer-MSFT 26,236 Reputation points

2022-05-04T16:42:04.047+00:00

Hello and welcome to Microsoft Q&A @Anonymous .

If I understand correctly, the issue is that the AM/PM part of your timestamp isn't being read correctly into ADF Data Flow. The format presented isn't a problem.

I don't have a Dataverse environment set up, so I'll make some csv with your example date to test on. This is making assumption this is not Dataverse/CDM specific. Is this acceptable?
Ryan Abbey 1,186 Reputation points

2022-05-05T03:45:12.043+00:00

Are you sure it's not just using your locale settings and showing you the time as per 24 hour clock? Have you got some modifications that are after 1pm to confirm?

Answer 1

MartinJaffer-MSFT 26,236

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

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

Hiltjo Slagter 1 Reputation point

2022-07-28T13:56:44.997+00:00

I ran into the same issue reading Azure Synapse Link exported data in a data flow.
For the dataflow Source, on the Projection tab, one can configure the format options. (Schema options)

When selecting MM/dd/yyyy hh:mm:ss a, and not selecting MM/dd/yyyy HH:mm:ss, the data is interpreted correctly.
Thuan Van Nguyen 0 Reputation points

2023-05-16T19:53:13.0166667+00:00

Are you uaing any specific derivation?

Share via

Wrong datetime format detection in Azure Data Factory Data Flows

1 answer

Your answer