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

3 thoughts on “Date Time Behavior and Format in Power Platform/Dynamics 365 CRM

  1. Pingback: Date Time Behavior and Format in Power Platform/Dynamics 365 CRM - 365 Community

  2. Pingback: Date Time Behavior and Format in Power Platform/Dynamics 365 CRM ERP for Hong Kong SME

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 )

Google photo

You are commenting using your Google 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