Wrong datetime format detection in Azure Data Factory Data Flows

Question

question

cfosund asked May 3, '22 HiltjoSlagter-6453 commented Jul 28, '22

Wrong datetime format detection in Azure Data Factory Data Flows

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!

azure-data-factory azure-synapse-analytics

image.png (31.2 KiB)

· 2

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MartinJaffer-MSFT · May 04 at 04:42 PM

Hello and welcome to Microsoft Q&A @cfosund .

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?

0 ·

RyanAbbey-0701 · May 05 at 03:45 AM

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?

0 ·

Answer 1 · 2022-05-06T18:10:38.867Z

MartinJaffer-MSFT answered May 6, '22 HiltjoSlagter-6453 commented Jul 28, '22

I have some findings to explain @cfosund

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.

image.png (41.1 KiB)

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HiltjoSlagter-6453 · Jul 28 at 01:56 PM

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.

0 ·

afbeelding.png (99.5 KiB)

question