Tuesday, 26 April 2016

UTCDateTime field in AX & daylight saving

This is an addition to an old post "UTCDateTime field in AX & filtering" (http://mybhat.blogspot.my/2011/10/utcdatetime-field-in-ax-filtering.html).

When using the DateTimeUtil::newDateTime() method to create a datetime value, if you're using daylight saving, then you'll probably need to take extra care with the optional parameter in this method - Timezone.

Let's walk-through with an example.
Today is 26/04/2016, and this year the daylight saving ends on 30 Oct for UK.
The job below generate datetime value from 25 Oct to 5 Nov, with the time a minute before midnight (11:59:59pm). One with the Timezone parameter and the other one without.

Below is the data if you view it in AX.
You'll notice the field "DateTimeTest" for records between 26/10 - 30/10 isn't showing 11:59:59pm, instead, it is showing 12:59:59am, Then looking at the field "DateTimeTestWithTimezone", it correctly display all the time value at 11:59:59pm.

And if you look at the backend data in SQL, it was the other way round, the "DateTimeTest" field saved in all the record has the time value of 23:59:59.000, which is different from how it is shown in AX. If you look at the "DateTimeTestWithTimezone", for records between 25/10 - 29/10, it is saved with 22:59:59.000 and records after 29/10 is saved with 23:59:59.000.

AX will show time value based on your computer settings.
If you use daylight saving, it will automatically convert the time accordingly.
In the above example, the field "DateTimeTest" is assigned with a datetime value generated without the timezone parameter, so it is saved as 23:59:59.000 in SQL, when this value is shown in AX at a machine with daylight saving, it automatically fast forward an hour for the dates before the daylight saving ends, hence, 23:59:59 (11:59:59pm) becomes the next day 00:59:59 (12:59:59am).

It works the same way if user enter a datetime value. Says, user enter 01/05/2016, 5:00:00pm with a machine with daylight saving on, this value will be saved as 01/05/2016, 4:00:00pm in SQL.

Enjoy playing around with UTCDateTime!!!