How to work with time zone issues in CRM date field

Problem

Date fields are entered in CRM as the logged in user’s local time but stored as UTC. When we save a record, it shows the current time but in the back end it stores the time in UTC. This is a problem, if we want to check expiry dates or validate time based on the date field. When running a CRM Work flow the system’s local time is also in UTC. This results in date and time conflicts.

For example, if the dates are entered before 10am of Day 1 in Australia, it may be stored as Day 0’s date (AU time zone is ahead of UTC) . It depends on the time zone setting.

If we want to check the record validation based on date,  it might give us false result. For example, if visa of person A is valid till 12:00am mid night of 19 Sep 2019 in actual – in system the validation date might be 2:00pm 18 Sep 2019.

Additionally, whenever we show these dates to the users (e.g. when their visa is expiring) that may be a day off due to the UTC time zone conversion done by CRM when inputting the fields.

Root Cause

The main issue arises because CRM converts date entered into the CRM UI (e.g. 19/9/2019 12:00AM) to UTC (e.g. 18/9/2019 2:00PM) when storing it.

When executing workflows CRM cannot convert the date back to local time as it has no UTC offset to use (it doesn’t know which time zone to convert to). The CRM condition operators “OnOrBefore” and “OnOrAfter” only look at the date section of the DateTime objects and ignores time.

So if we’re checking a From date 19/9/2019 12:00am (date entered into CRM, saved in UTC as 18/9/2019 2:00pm) so on 18/7/2019 3:00pm (today’s date, in the CRM it will be UTC so 18/9/2019 5:00AM). The comparison results in a false positive because CRM is only looking at the dates and sees that both are on the 18/9/2019 so returns this as valid when actually it’s not valid until tomorrow.

Recommended Solution

We can set date fields to work independently of time zones. It’s an option on the date and time field however once set it cannot be undone. This option means CRM doesn’t convert the value entered to UTC and just returns whatever is set.

Additionally  there should be a consistent time zone,  which can be used as the execution time. For this a TimeZoneUtility can be created to convert date and time to system time zone, stored in the Dynamics 365 system settings.

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.