Date Time Behavior and Format in Power Platform/Dynamics 365 CRM

Date Time field in power apps has behaviors and formats which control how the data will be stored in CDS (Common Data Service) and how it will pe presented to different users from different time zone on retrieval. Here I will try to explain the same in a simple way with examples for each combination of behaviors and formats.

Behavior: User Local

1. Format: Date Only
   • User’s provided date gets converted to UTC before it gets saved in CDS.
   • As there is no time part in user input system will take midnight as input time for conversion.
   • Stored data in CDS will have both date and time part.
   • System will convert UTC time to requesting user’s time zone while displaying data in UI.
2. Format: Date and Time
   • User’s provided date and time gets converted to UTC before it gets saved in CDS.
   • Stored data in CDS will have both date and time part.
   • System will convert UTC time to requesting user’s time zone while displaying data in UI.

Behavior: Date Only

1. Format: Date Only
   • User’s provided date gets saved in CDS as it is.
   • Same date value gets displayed to all users irrespective of their time zone settings.

Behavior: Time-Zone Independent

1. Format: Date Only
   • User’s provided date gets saved in CDS as it is, and system append midnight in the time part.
   • Same date value gets displayed to all users irrespective of their time zone settings.
   • Stored data in CDS will have both date and time part (time part will always be 00:00:00).
2. Format: Date and Time
   • User’s provided date and time gets saved in CDS as it is.
   • Stored data in CDS will have both date and time part.

Example:

I took this example to illustrate how values are stored in CDS and how it gets converted to different time zone based on user’s time zone settings.

• For easy understanding I used all times in 24hr format.
• This example shows what is the impact of user’s time zone on insert/ update/retrieval of date values,

Field Behavior	Field Format	Example
		Insert by User 1 (Time zone – GMT + 4:00).		Value seen by User 1 on UI (Time zone -> GMT + 4:00).	Value seen by User 2 on UI (Time zone -> GMT – 10:00).	Value seen by User 3 on UI (Time zone -> GMT + 13:00).
		Value entered in UI	Value stored in DB (UTC)
User Local	Date Only	6-Sep-20	9/5/2020 20:00	06-Sep-20	05-Sep-20	06-Sep-20
	Date and Time	05-Sep-2020 06:00:00	05-Sep-2020 02:00:00	05-Sep-2020 06:00:00	04-Sep-2020 16:00:00	05-Sep-2020 15:00:00
Date Only	Date Only	4-Sep-20	04-Sep-2020.	04-Sep-20	04-Sep-20	04-Sep-20
Time-Zone Independent	Date Only	3-Sep-20	03-Sep-2020 00:00:00	03-Sep-20	03-Sep-20	03-Sep-20
	Date and Time	02-Sep-2020 10:30:00	02-Sep-2020 10:30:00	02-Sep-2020 10:30:00	02-Sep-2020 10:30:00	02-Sep-2020 10:30:00

Here is some important point to note

• Once a date time field behavior is set to either “Date Only” or “Time-Zone Independent” it cannot be changed.

  

• Date time field with behavior “User Local” can be changed to “Date Only” or “Time-Zone Independent”. You must evaluate the impact before you make this change. Also, you must review all process like BPF/Workflow/Power Automate etc. and re-activate it to reflect field behavior changes in the process.

  

• If you change date time field behavior “User Local” to “Date Only” or “Time-Zone Independent”, it will only affect new data modifications any existing data stored in this field will not get affected.
• Within a behavior one can switch the format at any time. You can change format from “Date Only” to “Date and Time” and vice versa.

  

• “Behavior: Date Only, Format: Date Only” and “Behavior: Time-Zone Independent, Format: Date Only” behave similarly except one thing that later have time part (always 00:00:00) when it is saved in CDS.

Thanks for reading, hope you find it helpful.

Cheers