Date time behavior in Dynamics 365 determines what value will be stored in database and what will be displayed on interface. Dynamics 365 supports below behaviors for date time fields:
- Date only – where only date portion is stored with time value as 00:00:00 with no time-zone information
- Time-Zone Independent – where date and time both are stored but without time-zone information. Time value does not change based on logged in user time-zone settings
- User Local – where date and time both are stored in UTC. Time value changes based on logged in user time-zone settings
You can refer article which clearly explains how values are stored in SQL DB versus shown on Dynamics interface for each datetime behavior.
In this blog we will focus on what precautions to be taken while migrating datetime fields with different behaviors from legacy system to Dynamics 365.
Migrating Date Only Datetime fields:
Since Date Only datetime fields store time value as 00:00:00 regardless of value passed during migration, no changes or conversions required for Date Only fields.
Migrating User Local vs Time-Zone Independent Datetime fields:
For User Local datetime fields, value is stored in UTC format in database and will be formatted on interface based on user’s time zone whereas for Time-Zone Independent fields, datetime value is stored without time-zone information and will not be formatted when displayed on interface.
As shown above, “Last Modified” field is User Local datetime field where value varies based on user’s time-zone whereas “Established On” field is Time-Zone Independent where value remains same.
- While migrating User-Local datetime fields, convert value to UTC first if legacy system is in different time-zone than UTC so that UTC value is stored in SQL DB and formatted value (actual legacy value) will be displayed on interface
- While migrating Time-Zone Independent datetime fields, conversion is not required and actual legacy value passed will be stored and displayed as it is on interface
In C# code, condition needs to be added to check for DateTimeBehavior property of datetime field and apply UTC conversion if it is User local as below: