Dynamics CRM - Date and time Fields

Introduction

This article documents the basics of using Date and time fields in Dynamics CRM.

Basic Concepts

(1) Dataverse stores all date and time values in the UTC time zone. When the application displays values or processes user-entered values, Dataverse and model-driven applications can use the “Format” option to adjust to the user’s time zone.

（2）When an entity creates a new Date and time' field, the following format can be selected:

(3) When an entity creates a new Date and Time field, the following Time Zone Adjustment option is available in the Advanced Options.

In addition, the available options for Time Zone Adjustment vary according to the Format as follows:

Format When Date only is selected, Time Zone Adjustment can be selected:

Format When Date and time is selected, Time Zone Adjustment can be selected:

Can Format and Time zone adjustment be changed again?

Is it still possible to modify the format and Time zone adjustment of a Date and time field that has already been created?

The results are verified as follows:

How should I choose the Time zone adjustment?

Small experiment

I’ll add the following five new date and time fields to the Invoice entity, assign values to them, and then use them to see the original values stored in the database

（1）New Fields

The 5 ‘Date and time’ fields are as follows:

（2）Fill in the “Date and Time” field in the form

Assigns a value to a field in the (GMT+08:00) Beijing, Chongqing, Hong Kong, Urumqi time zone:

（3）Viewing data in the database

The results are as follows:

SELECT gdh_d_userlocal AS 'D - User Local',
       gdh_d_tz_independent AS 'D - TZ independent',
       gdh_d_d AS 'D - D',
       gdh_dt_userlocal AS 'DT - User Local',
       gdh_dt_tz_independent AS 'DT - TZ independent'
FROM   gdh_invoice
WHERE  gdh_no = 'SAS-00000001';

（4）Viewing Data with Test06

Note: The time zone used by user Test06 is (GMT+04:00) Baku.

（4）Use the Client API to get

a. Get D - User Local and DT - User Local value.

# 'Sun, 09 Mar 2025 16:00:00 GMT'
Xrm.Page.getAttribute("gdh_d_userlocal").getValue().toUTCString(); 

# 'Sun, 09 Mar 2025 16:00:00 GMT'
Xrm.Page.getAttribute("gdh_d_userlocal").getValue().toUTCString(); 

b.For “Time zone independent”, the timezone of the browser is returned

Get D - TZ independent and DT - TZ independent value.

# 'Wed Mar 12 2025 00:00:00 GMT+0800 (China Standard Time)'
Xrm.Page.getAttribute("gdh_d_tz_independent").getValue().toString(); 

# 'Mon Mar 24 2025 09:30:00 GMT+0800 (China Standard Time)'
Xrm.Page.getAttribute("gdh_dt_tz_independent").getValue().toString(); 

（5）Using Web API to Retrieve Data

Assign values to fields in the (GMT+08:00) Beijing, Chongqing, Hong Kong, Urumqi timezone and retrieve them using a Web API:

It can be observed that the raw value retrieved via the Web API is the same as the value stored in the database.

var req = new XMLHttpRequest();
req.open("GET", Xrm.Page.context.getClientUrl() + "/api/data/v9.1/gdh_invoices(98F83878-1E35-EF11-8409-0017FA0671FA)?$select=gdh_d_d,gdh_d_tz_independent,gdh_d_userlocal,gdh_dt_tz_independent,gdh_dt_userlocal", true);
req.setRequestHeader("OData-MaxVersion", "4.0");
req.setRequestHeader("OData-Version", "4.0");
req.setRequestHeader("Accept", "application/json");
req.setRequestHeader("Content-Type", "application/json; charset=utf-8");
req.setRequestHeader("Prefer", "odata.include-annotations=\"*\"");
req.onreadystatechange = function() {
    if (this.readyState === 4) {
        req.onreadystatechange = null;
        if (this.status === 200) {
            var result = JSON.parse(this.response);
            var gdh_d_d = result["gdh_d_d"];
            var gdh_d_tz_independent = result["gdh_d_tz_independent"];
            var gdh_d_userlocal = result["gdh_d_userlocal"];
            var gdh_dt_tz_independent = result["gdh_dt_tz_independent"];
            var gdh_dt_userlocal = result["gdh_dt_userlocal"];
        } else {
            Xrm.Utility.alertDialog(this.statusText);
        }
    }
};
req.send();

Query Operators Not Supported?

For “date-only” type date and time fields, the following query operators are not allowed. Using these operators in queries will result in an invalid operator exception error.

• X minutes ago
• X hours ago
• Past X hours
• Next X hours

For example, when filtering the “D-D” column, the filter conditions are as follows:

<condition attribute="gdh_d_d" operator="last-x-hours" value="5"/>

An error is reported after execution：2147779605The operator is not valid or it is not supported.

How to Set the User’s Time Zone?

Click the Settings button in the top right corner –> Personalization Settings –> General Tab –> Select Time Zone.

Refer

1. Column Data types
2. Column Data types