Friday 7 October 2011

UTCDateTime field in AX & filtering

When doing filtering at UTCDateTime field in AX, it needs extra caution and consideration. Reason was the timezone & daylight saving. In AX, the UTCDateTime appears to be one field, but it is saved as two separate field in SQL - an extra field with the same name postfix with "TZID".


UTCDateTime field in AX is stored as two field in SQL

When apply filter with a date, it only filter the datetime field, ignoring the timezone.


Eg.
The server time zone is London, with daylight saving.
When insert a value into UTCDateTime field in AX, it stores the value in SQL without the daylight saving.


- AX: "08/10/2011 00:30:00" (one hour ahead due to daylight saving)
- SQL: "07/10/2011 23:30:00" (stored in SQL without daylight saving)


The screenshots below shows some example of the data and result after apply the filter


Filter with "07/10/2011 00:00:00"
Records with "08/10/2011 00:30:00" appears in the result, it was due to the
actual value stored in SQL is "07/10/2011 23:30:00" which met the filtering criteria

Filter with "07/10/2011 00:00:00".."07/10/2011 23:59:59"
(this filtering value above is automatically converted by AX when "07/10/2011..08/10/2011" is entered)

Showing the datetime value stored in SQL

The actual query sent to SQL Server
SELECT A.TESTDATETIMEFIELD,A.TESTDATETIMEFIELDTZID,A.RECVERSION,A.RECID FROM TABLE2 A WHERE ((DATAAREAID='ceu') AND ((TESTDATETIMEFIELD>='2011-10-07 00:00:00') AND (TESTDATETIMEFIELD<='2011-10-07 23:59:59'))) ORDER BY A.DATAAREAID DESC,A.TESTDATETIMEFIELD DESC OPTION(FAST 1)


If date time filtering is crucial in your solution, you'll need to take extra caution on this.

2 comments:

  1. Hi WODOY,
    I am working on a problem involving an application(not in AX) where I need to display the AX datetime to users.
    I query the AX database to get the information, and I am retured my datetime column, and the datetimeTZID column. I need to then convert that datetime column from UTC using the datetimeTZID column.
    My problem is that I don't know what timezone that TZID corresponds to. From your example above, what is time zone is 35001.
    Thanks,
    Brent

    ReplyDelete
  2. Unfortunately I'm not sure about the TZID value as well.
    You can try loop through the enum to gets its value, but it appear different than the one stored in database. Eg. The example I have, it is 35001, my timezone is London, which in the enum it is 35.

    You probably can try insert a time with each different timezone and check their value.

    static void InsertTimeZoneData(Args _args)
    {
    DictEnum sysDictEnum = new DictEnum(enumNum(TimeZone));
    int counter, element;
    ;

    element = enumCnt(TimeZone);
    for(counter = 1; counter <= element; counter++)
    {
    info(strFmt("TZID: %1, Label: %2", sysDictEnum.index2Value(counter), sysDictEnum.index2Label(counter)));
    }
    }

    ReplyDelete