AnsweredAssumed Answered

XML export DATEFORMAT attribute does not work as expected

Question asked by comment on Oct 23, 2018
Latest reply on Oct 29, 2018 by TSGal

AFAIK, this affects all versions of FileMaker Pro since the XML export feature was introduced, on both platforms.

 

Background:

When you export records as XML using the FMPXMLRESULT grammar, the exported file includes a DATEFORMAT attribute that specifies the format of dates used in the XML document.

 

The help file has a neat table showing how the value of the DATEFORMAT attribute is to be constructed based on the date format used in the exported file:

 

Field

Full form

Short form

Year

yyyy (4 digits)

yy (2 digits)

Month

mm (2 digits)

M (1 or 2 digits)

Day

dd (2 digits)

d (1 or 2 digits)

 

Problem:

The actual content of the DATEFORMAT attribute does not follow the rules specified in the help. Here are the results of  some tests I have performed, using various date formats. The actual date used in these tests is January 2nd, 0003.

Format in useDate exported asDATEFORMAT shows
Expected
YYYY-MM-DD<DATA>0003-01-02</DATA>DATEFORMAT="Yyyy-m-d"yyyy-mm-dd
YYYY-M-D<DATA>0003-1-2</DATA>DATEFORMAT="Yyyy-m-d"yyyy-M-dd
MM/DD/YYYY<DATA>01/02/0003</DATA>DATEFORMAT="M/d/yyyy"mm/dd/yyyy
M/D/YYYY<DATA>1/2/0003</DATA>DATEFORMAT="M/d/yyyy"M/d/yyyy
DD/MM/YYYY<DATA>02/01/0003</DATA>DATEFORMAT="D/m/yyyy"dd/mm/yyyy
D/M/YYYY<DATA>2/1/0003</DATA>DATEFORMAT="D/m/yyyy"d/M/yyyy

 

As you can see, the DATEFORMAT attribute shows no difference between the full and short forms of both month and day. The long form for month should be "mm". Instead, it is always "m". Likewise for day, the long form should be "dd" but it is always "d".

 

The capitalization is also confusing: according to the help, only the short form for month should be capitalized ("M" instead of "m"). Instead, the first character of the entire string is always capitalized, regardless of the format.

 

This makes it very difficult to handle the exported file when the actual date format used by the file is not known in advance (and it will be unknown when the file is set to use the user's OS date format).

 

How to replicate:

Change the OS short date format to a format of your choice.

Using any file, set the file to use current system settings.

Export as XML using the FMPXMLRESULT grammar and observe the contents of the DATEFORMAT attribute in the resulting XML document.

Outcomes