June 2nd, 2011
For a recent project, I needed to export the object properties of a number of objects to an XML file.
As you know, in the Object table, there are separate columns for Date and Time. Initially, I exported each column as an individual XML attribute, using the FORMAT function’s standard format #9 to XML-format the values. According to the C/SIDE on-line help, Standard format 9 is used to display the data in the standard XML formats, thus making these values as culture-independent as possible. To achieve this, time values are always expressed as UTC (Coordinated Universal Time, previously known as GMT).
My code looked something like this:
MyElement.setAttribute('date', FORMAT(MyObject.Date, 0, 9)); MyElement.setAttribute('time', FORMAT(MyObject.Time, 0, 9));
Despite looking perfectly valid (at least it did to me, initially), this will lead to results that are just plain wrong. Let me show you why, using the following code sample:
MyDate := 311210D; MyTime := 080000T; MyDateTime := CREATEDATETIME(MyDate, MyTime); MESSAGE(FORMAT(MyDate, 0, 9) + ' ' + FORMAT(MyTime, 0, 9)); MESSAGE(FORMAT(MyDateTime, 0, 9));
The first message box reads: 2010-12-31 06:00:00Z
The second message box reads: 2010-12-31T07:00:00Z
From where I’m writing this, we are one hour ahead of GMT in winter, and two hours during the summer, thanks to Daylight Saving Time. The FORMAT of MyDateTime handled that situation correctly: on the 31st of December last year, we were ahead only one hour. The FORMAT of MyTime did not have any date information to base its conversion on, and had no choice but to use today’s date (and corresponding time difference of 2 hours), causing the information in my XML file to differ from the actual object properties in my database.
In the next sample, the problem is even more extreme:
MyDate := 010111D; MyTime := 000000T; MyDateTime := CREATEDATETIME(MyDate, MyTime); MESSAGE(FORMAT(MyDate, 0, 9) + ' ' + FORMAT(MyTime, 0, 9)); MESSAGE(FORMAT(MyDateTime, 0, 9));
The first message box reads: 2011-01-01 22:00:00Z
The second message box reads: 2010-12-31T23:00:00Z
Thanks to the incorrect conversion, in the first message box, GMT appears to be 10 hours ahead of us! I guess from now on, I’ll be using DATETIME’s whenever I’m formatting dates and times in XML-format.