web
You’re offline. This is a read only version of the page.
close
Skip to main content
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Blogs / Dynamics 365 Data Integration Blog / How to solve CDS to D365-FO...

How to solve CDS to D365-FO date format and timezone issues

Views (1855)
09fd18dc4ea8421c809efacbbdc59ee5 Profile Picture 09fd18dc4ea8421c809... 794

If you using Microsoft Dynamics 365 Finance and Operations Apps and not using a non-US date format and time-zone you might get issues when synchronizing data from Common Data Service (CDS) entities when such data includes date or date/time fields. This was experienced in one of my projects where we were synchronizing data between some CDS entities and some FinOps entities but the result was that an 02/03 (2nd March) in CDS was becoing 03/02 (3rd February) when the data lands in D365-FinOps and viewed from one of the FinOps apps. Also some "date only" data types (such as those originating from Dynamics 365 HR / Talent) were being moved 1 day backward in FinOps because CDS adds midnight time to such a "date only" data type and this was being moved 1 hour back and thus also one day before at 11pm.

The two above issues were happening even though both CDS and FinOps were correctly configured with a European date format and time zone. From FinOps side we verified the user options of the data integrator account and the System Parameters form and all looked good. In our opinion this was a bug in the standard data integrator tool and thus we were on the verge of trying to solve this by adding code to the data entities which contain date fields. However, this was not a scalable solution as more data entities with dates would need to be integrated down the line.

Luckily, this was avoided and we found a work-around which can be used until this tiny flaw in the data integrator is fixed. The work-around is as follows:

1. First verify the Source Data Format being used by the project being created by the Data Integrator tool. In this case it is CSV-Unicode.

1.jpg

2. Then go back to the Data Management main screen and click on "Configure data source" tile

2.jpg

3. Within the grid click on the "CSV-Unicode" record (from step 1 above)

3.jpg

4. Within the "Regional settings" tab ensure that language local is set to "en-us" and that Timezone is set to "GMT"

4.jpg

The above steps should help you eliminate any date and timezone conversion issues between CDS and FinOps when using the data integrator tool.

Hope this helps.

Best Regards,

Mirko Bonello

Comments

*This post is locked for comments

  • Community Member Profile Picture Community Member
    Posted at
    I am experiencing this same issue with Dual Write initial sync and live sync. F&O date field (just a date field, not time) is exported into Data Entity with time 00:00:00. CE/Dataverse receives that data and posts to correct field, but date is one day behind. It doesn't matter what time of day we run this (I am in US Eastern time, so several hours behind GMT, but this happens even when run in the morning, when date is same date as GMT. ) I was excited to try the suggestion above. I can see the DMF jobs in F&O for the initial sync used CSV-Unicode, and there the timezone was blank. But filling in GMT Greenwich Mean time didn't change the live sync behavior. I haven't tested a new initial sync yet. Perhaps the Live sync uses a different data source? Does anyone out there know for sure so I'll know if my test is valid?
  • Tony zhong Profile Picture Tony zhong 897
    Posted at
    Facing the same issue recently. When my Dataverse recorded the correct time for example : 26-10-2021 04:00:00 PM, and my Timezone is GMT+8, in F&O recorded as 25-10-2021 00:00:00. I feel excited finding this blog then changes Source Data Format to GMT (Coordinated Universal Time), but turns out not working. This error only happened when the creation was from Dataverse and not vice versa. Anyone manage to find the perfect solution for this ? Agreed on Mirko as well that we cannot handle it by writing code in its Data Entity all the time, coz more and more entity will come. Btw, for my case, it is from custom table in Dataverse to LedgerJournalTrans_Project in F&O. Hope someone can give an idea. Many thanks.
  • Lucky Dog Profile Picture Lucky Dog 670
    Posted at
    I have the same issue with transactions going from Project Ops into FO. Transactions in FO are dated a day earlier than in Dataverse. I suspect that sees a date as being at 00:00 hours on that date and converts it to a US time zone which takes into the previous day. Has anyone managed to fix this yet?
  • Higo Cidario Profile Picture Higo Cidario
    Posted at
    Hi Mirko, Very nice post! We are experiencing the same issue in terms of the date coming into FO hour journal lines one day earlier from D365 PSA actuals. I understand why changing the locale fixes your problem with switching month and day. The problem with dates coming in on hour journals lines remains after several changes in the "source data format". I tried multiple time zone preferences(manual, company, auto), with DIXF/batch service resets in between. The D365 CE API returns datetime formats in UTC time causing a day early at 23:00 ( for GMT+1 users). Basically the date seems to be saved incorrectly in D365 PSA database. I see no alternative but to apply a new date only field in D365 PSA, or do you have some more information on how you solved this issue?