blogs & Things

How To Fix DateTime Stamps In Microsoft Dynamics 365

Craig Seymour – Dynamics Practise Lead, cloudThing

Our dedicated testers have recently been reporting a one day offset between two custom date fields.

 

Some of the quality assurance team in our cloudThing India offices have recently started noticing a one day offset between two custom date fields (‘Contract Start Date’ and ‘Invoice Schedule Start Date’) in Opportunity, even though the second was being set as a direct copy of the first.

Looking into the problem a little deeper I realised that the Contract date was set as a ‘normal’ DateTime field but with Time not shown in the UI, whilst the Invoice date was set as a TimeZone independent DateTime field.

Once I knew what the problem was all I had to do was find a solution… ah the fun bit!

 

The Contract Date

When a user creates a Date without a timestamp in Microsoft Dynamics 365 (or any model-driven Power App) through the User Interface (UI), it will automatically assume a timestamp of 00:00 in their current locale (local time zone preference and daylight saving).

However, when it writes that date to the database, it converts it to the UTC value, taking into account the user’s locale.

So for example, dates created in India will be 18:30 the day before.

When Dynamics 365 then reads it back out, it does the reverse.  So, from the Indian user’s point of view, the displayed date is always correct.

Continuing with the example, that means for records created in India, the Indian user sees in the UI 2020-01-20 (with @ 00:00 hidden) but in the database a user in the UK would see 2020-01-19 @ 18.30.

That means if ‘I’ look at this record in D365 from the UK I actually see ‘2020-01-19’ because my TimeZone correction (from UTC to BST) is +1 hour, so Microsoft Dynamics 365 is displaying 19-01 (with @ 19:30 hidden).

The Invoice Date

When the field is of type TZ independent, Dynamics 365 ignores the locale and stores in the database exactly the same date as you see in the UI.

As for the Contract date, Dynamics 365 assumes a timestamp of 00:00 and then stores this without correction as 2020-01-20 @ 00:00.

When this is read in India, the UK, or anywhere else for that matter, Dynamics 365 will show us 2020-01-20.

Copying the Contract Date To the Invoice Date

When we create the Invoice date we’re doing a straight copy (using the API) of the field value ‘from the database’ from a field which Dynamics 365 knows it should apply the correction for locale to it, to one which it knows it shouldn’t.

So our database value is 2020-01-19 @ 18.30 and Dynamics 365 studiously ignores the 18:30 part and simply shows us 2020-01-19 in the UI.

And the fix?

Well in theory we should just do the same as Microsoft Dynamics 365 (by that I mean apply the locale correction before writing the Contract date) but in practice we can’t because Dynamics 365 doesn’t store the locale the data was created, just the UTC DateTime, so we don’t have the data we need to do this.

One hack which might work would be simply to ’round’ the Contract date up or down to the nearest midnight when we copy it, which will work everywhere except for dates created by users in New Zealand, Eastern Siberia and a handful of islands in the Pacific.

Or…

We could convert all Contract dates to have a timestamp of noon; again this works for most Time Zones except New Zealand, Eastern Siberia and a handful of islands in the Pacific again.

If you’re in the UK (or any other country in the GMT timezone) and looking for another solution you could simply convert the Contract date to TZ independent.  Luckily for us, GMT and UTC are exactly aligned, so all our UK created dates happen to be set at 00:00 anyway!

The downside is that all the dates created by the QA Team in India will start to show a day early.

More blogs & Things

More blogs & Things


James Crossland in NonProfit

AI + Automation: Reducing Donor Churn & Maintaining Sponsor Interest

Churn management is a vital element of any marketing strategy, and the NonProfit sector is no exception. Knowing what to track and having a joined up view of all your donations data is vital for getting this right, and also opens the door to building innovative data-driven campaigns.   At our recent DataScience and Transformation in Charities […]


James Crossland in NonProfit

Dynamics 365 In NonProfit’s

Charities have unique funding concerns, and an obligation to spend as much as possible on their chosen cause. However, an investment in technology can offer ROI in the form of more than just improved fundraising. Dynamics 365 can help rework complex business processes, ensure compliance with stringent safeguarding and financial regulations, as well as consolidate […]


James Crossland in Tech

8 Ways Your Business Can Increase Turnover With Big Data

Understand how Big Data and Data Science can transform your business…   Big Data is the phrase that’s used to categorise any data that’s too large, complex, cumbersome or complicated to be managed and processed by conventional technology. To put that into a relatable context; being able to recommend your customers content, products or offers based […]


James Crossland in NonProfit

How To Reduce Donor Churn In NonProfits

Reducing Donor Churn doesn’t have to be a big task but does need to be a fundamental part of a NonProfit’s day to day processes   What Is Donor Churn? Donor Churn is the likelihood of an individual stopping their donations to a charitable cause for a variety of different reasons resulting in the non-profit organisation […]


James Crossland in Tech

Agile: Cutting Costs, Improving Quality & Accessing Talent

After using Agile to develop software products for several years, we thought we’d share the challenges we encountered at the start, what we did to change and the results we saw (which were ultimately uplifts in quality and efficiency)…   My development team has been using Agile to develop software product since 2007. Personally, I’ve seen many […]


James Crossland in Tech

UI VS UX

What’s the difference between UI and UX?   Simply put UI (or User Interface) are the pages, screens, buttons, icons and any other visual aspects of a website or App that let you interact with it… or to expand on that into the non-virtual world… UI is how you experience using something – For instance in opening a fridge, […]