(Mac only users need not really bother with this).
One of the problems with Word and dates is that whatever the format used
by the data source to store dates, by the time Word gets its data from
the data source, it is typically actually in text format. So you don't
actually have the opportunity to deal directly with a numeric date value.
That means that whatever method is used to get the data from the data
source has to recognise date data reliably, and you also have to be sure
that if you use, for example
{ MERGEFIELD \@D } to extract the day of the month, that will work
whether Word displays the date as
D/M/Y
or
M/D/Y
This, AFAICS, is the reason for a rather nasty kludge in recent versions
of Word - which is that Word actually changes its interpretation of date
strings such as 2/9/2009 depending on whether or not it is connected to
a data source using OLE DB.
Because I've always had a somewhat arbitrary experience trying to look
at this stuff, I would be interested to see if those set up with Windows
systems where the date format is DD/MM/YYYY have the same experience as
I do.
To do that, what you need is
a. (say) Word 2007 or Word 2003
b. A Word file, not connected to any data source, with the following
fields:
{ SET X "2/9/2009" }
{ X }
{ X \@"DD MMM YYYY" }
AFAICS when you update and toggle those fields, you should see
2/9/2009
2/9/2009
02 Sep 2009
Then connect to (say) an Excel data source. If you are offered the
choice, choose the OLE DB Databases option. Here, I see the 02 Sep 2009
change to 09 Feb 2009
So if your data source was an excel file containing
k d
1 2/9/2009
(i.e. that's a proper Excel format date with D/M/YYYY format, i.e. 2nd
Sep 2009) and you inserted
{ MERGEFIELD d }
{ MERGEFIELD d \@"DD MMM YYYY" }
and toggle the field codes, you should see
9/2/2009
02 Sep 2009
In other words, whatever your regional settings, with an OLE DB
connection, a date mergefield with no date format is displayed in a "US
format", which is a source of understandable irritation for many. I feel
sure that this is not the whole story, though.
X/Y/ZZZZ "literals" that could be either D/M/YYYY or M/D/YYYY are
assumed to be M/D/YYYY (or D-M-YYYY, M-D-YYYY, I don't think it makes
any difference) assumed to be M/D/YYYY.
Connecting via e.g. ODBC does not have this effect. So anyone designing
a merge application really has to be careful about introducing date
literals (however they are constructed) especially if they do not know
what type of connection wil be used. Personally I would always favour
constructing date/times specified in a YYYY-MM-DD style format on the
grounds that
a. they do not suffer from this particular problem (although you have
to be careful about how you compare them in IF fields)
b. they will sort well even with sort operations that don't know they
are dates.
However, at least Excel dates that are not stored as text are reasonably
unambiguous (forgetting about the 1904 date system). If you are getting
dates from a text file (in Windows), things are more complicated because
the Excel numeric format is not supported AFAIK (although you could
use a numeric format and use macropod's field stuff to coerce it into a
display format), so you would typically have to use a text format of
some kind. If Word opens the file using its internal text converter,
which it will if it can't do it using OLE DB (e.g. over 255 fields)
you'll see the dates as they are in the file. But the OLE DB
provider/ODBC provider interprets them at least partly according to your
regional settings (whereas of course in many cases the text file may
have been created on a system with different assumptions), and/or any
date format you may have specified in a schema.ini.
Peter Jamieson
http://tips.pjmsn.me.uk
You