UtcDateTime in Dynamics AX 2009

In Dynamics AX 2009, Microsoft introduced a new data type, UtcDateTime, that is going to eventually replace the 2 existing types, Date and Time, which are still present in the application right now.
Obviously, the introduction of this new type requires a tutorial on how it can be used on forms, how you can filter on fields of this type, as well as what functions are available out of the box for it.
So I have made such a tutorial, and I hope it will be useful for developers upgrading to AX 2009.

Download the xpo for the tutorial from my SkyDrive

The tutorial consists of a single form, containing the following elements:

• a grid, displaying data from CustTable
• 4 buttons for various filtering actions
• 3 controls that allow specifying the filtering conditions for the data

. In the form, you can see how UtcDateTime based controls are displayed both in a regular group and in a grid.



Below is an explanation of the implemented functionality, in form of a Question/Answer section:

1. Q: Can I filter on the new UtcDateTime type, specifying the Date part only?
   A: Yes. You simply have to specify only the date part when applying the filter, like below. Note, that this also works fine when filtering directly from the UI (Ctrl+F).
   

   qbdsCustTable.addRange(fieldNum(CustTable, CreatedDateTime)).value(queryValue(DateFilter.dateValue()));

   What is interesting is how the kernel processes this range. In the below infolog, you can see that when viewing the query, it displays a "==" condition on a specific dateTime value.
   But in reality, as you can see from the SQL trace, a range ">= && <=" condition is applied to span exactly one day.
   Also note, that the values in the trace are displayed accounting for the TimeZone I am in, as well as for Daylight Saving Time
   
   
2. Q: Can I filter on the new UtcDateTime type, specifying the Time part only?
   A: No, this is not possible with a UtcDateTime type. The range applied when specifying a Time value is the minimum DateTime value, as seen below. Note, that in the SQL trace it is converted to "no range".
   
   
3. Q: Can I use similar query functions for UtcDateTime type?
   A: Yes. All the main existing functions for working with QueryBuildRange also support UtcDateTime. For example, in the infolog below you can see how a range on 2 UtcDateTime dates is applied. Global::queryRange method was used to achieve that. Note, again, that the SQL trace offsets the DateTime by the appropriate number of hours based on my location.
   
   
4. Q: How is the UtcDateTime stored in the database? Is it displayed the same way on forms?
   A: The UtcDateTime fields are in the database always stored in Coordinated Universal time (UTC). Whenever displayed on forms and bound to table fields, the data is converted to the user's preferred timezone. Note, that you need to take care of the conversion yourself, if the control is not bound to a field. For an example, see the init method of the tutorial form.
5. Q: What standard helper functions are present for working with UtcDateTime type in the application?
   A: The main entry point for working with UtcDateTime type is the DateTimeUtil class. It allows adding Days/Months/Years, as well as applying an offset, getting the user's preffered timezone, converting from/to other types, etc. An example from the form init method is posted below:
   

       // getSystemDateTime() returns the current DateTime set in the system, not the current machine dateTime.
       // Note that getSystemDateTime() returns a UTC date and time, not your local date time.
       // In order to receive your local DateTime value, you should use methods applyTimeZoneOffset and specify the preferred time zone.
       utcDateTimeFilter.dateTimeValue(
           DateTimeUtil::applyTimeZoneOffset(
               DateTimeUtil::getSystemDateTime(),
               DateTimeUtil::getUserPreferredTimeZone()));

6. Q: Does this mean that the support for Date and Time types has been removed?
   A: No, Date and Time are still supported. As you can see in the form init method, SystemDateGet(), timeNow(), today() are all still supported
7. Q: I don't see the actual filter values in the SQL log. Instead, all I see are "?"'s. Also, how can I limit the number of data/fields selected from the database?
   A: This is just some extra stuff, not related to UtcDateTime, but still useful to know and pay attention to.
   CustTable has a very large number of fields, and I am only displaying 4 of those in the form, so it would be unwise to always query and return all of the fields. Luckily, the datasource has a property OnlyFetchActive, which controls the query behavior by only selecting the fields actually displayed on the form. Note, that you should avoid using this with editable datasources. See comments to this post for details
   As for "?"'s in the SQL trace - that is happening due to the use of placeholders. This in general optimizes the performance of the queries, by creating a query execution plan and storing it for future use. But it is possible, and is required in some specific cases, to force the use of literals (meaning the actual values of the ranges in the query). This can be done using the literals method on the query. See method init on the form for an example.