 |
| UTCDateTime field in AX is stored as two field in SQL |
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 |
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.
Hi WODOY,
ReplyDeleteI 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
Unfortunately I'm not sure about the TZID value as well.
ReplyDeleteYou 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)));
}
}