Date format DD/MM in application wrongly converted to MM/DD in Wor

[M Rizzo]

The date format is showing correctly as DD/MM in a new business application
we are testing. The date format is being wrongly converted to MM/DD in Word
mail merge when DD<13 e.g. 06/05/2009 (6th May 2009) is being converted to
05/06/2009 (5th June 2009) in Word mail merge despite using a selection of
date field switches including
{ MERGEFIELD date \@ "MMMM d, yyyy"} and { MERGEFIELD date \@ "D MMMM YYYY"}
.. The issue seems to be that we prefer to work using UK date format (i.e.
DD/MM/YYYY). The business application developers who normally write the
business application for US customers assure us that they have switched the
application to show and export in UK date format. Assuming that they have
done this correctly, is there something in our environmental settings which
we need to change. Our environment is as follows. SERVER 1: where the
business application is installed, SQL 2008, MS Offiice 2003, OS 2003. SERVER
2: application server, Windows 2003 with Office 2000. Apparently the 2
servers are not connected. Please help!

 

[Peter Jamieson]

NB, you have landed in a Mac Word group here...

This is just a partial reply - I'll try to be more precise when I have a
bit more time to check some results.

Although this problem is known when the data comes from Access/Excel via
the OLE DB provider, I haven't heard of it before when the data source
is SQL Server. Nor do I know the fix off the top of my head. However,
what Word sees is actually just a string. If your MERGEFIELD field,
without any date format switch, returns a date like

DD/MM/YYYY or MM/DD/YYYY

then there is problem because Word does not have any information about
which is the MM and which is the DD.

What you really need is for OLE DB and/or ODBC to return either
a. exactly the date that you need, and apply no switches (if necessary
you can achieve that as long as you are willing to create SQL Server
Views that use CAST/CONVERT to convert the date to a string or
b. a date in the format YYYY-MM-DD or YYYY.MM.DD, which I believe Word
will always interpret correctly - i.e. you will still need to use a date
switch but it should always work.


Peter Jamieson

http://tips.pjmsn.me.uk

 

[John McGhie]

Well, we can't be much help to you in here, because you have come through to
the Macintosh group. The answers you need are very specialised for Windows.

The PC Word groups are here:

http://www.microsoft.com/office/community/en-us/flyoutoverview.mspx


However: Conventionally, a Windows application sets its date format from
the system, and that has to be set in three different places in Windows to
make it happen reliably.

The safest thing to do is to REMOVE the English US definition from Windows,
to set the Word default templates to English UK, and to then remove the
Language toolbar from Office. Otherwise, English US will keep coming back
like a bad penny.

Given that this operation is too technically complex to expect normal
desktop users to complete reliably, it's a change you should really have
your System Administrator make: they need to hit both servers and each
desktop. Which they can do automatically with a script.

I think it is also important to have sharp words with the business
application developer about the necessity to SET the correct date formats
when operating in languages other than English USA.

Tell them, from me (an Australian) that the "conventional" Windows
programming practice of "inheriting" the language and date formats from "the
system" and expecting the system to be set correctly is simply not good
enough. They must explicitly set it, and check it's correct before they use
it.

There is another change you can make, which I have learned from bitter
experience pays big dividends: Adopt a date format that always uses letters
for the month. That way, you can tell at a glance if it is wrong

Relying on Windows date handling is simply not reliable outside the USA.
Microsoft doesn't care: if it works in the USA they're happy. The rest of
us have to exert care, attention, and effort to get the correct date formats
to set and stick. If you take your eye off the problem for an instant,
"somewhere" it will revert to the inside-out American date that is used in
only ONE of the 191 countries of the world.

Hint: Do your paper sizes at the same time, or they will be constantly
reverting to that stupid "Letter" size you can buy in only one country in
the world

Hope this helps


On 25/06/09 8:00 PM, in article
(e-mail address removed), "M Rizzo" <M

The date format is showing correctly as DD/MM in a new business application
we are testing. The date format is being wrongly converted to MM/DD in Word
mail merge when DD<13 e.g. 06/05/2009 (6th May 2009) is being converted to
05/06/2009 (5th June 2009) in Word mail merge despite using a selection of
date field switches including
{ MERGEFIELD date \@ "MMMM d, yyyy"} and { MERGEFIELD date \@ "D MMMM YYYY"}
. The issue seems to be that we prefer to work using UK date format (i.e.

DD/MM/YYYY). The business application developers who normally write the
business application for US customers assure us that they have switched the
application to show and export in UK date format. Assuming that they have
done this correctly, is there something in our environmental settings which
we need to change. Our environment is as follows. SERVER 1: where the
business application is installed, SQL 2008, MS Offiice 2003, OS 2003. SERVER
2: application server, Windows 2003 with Office 2000. Apparently the 2
servers are not connected. Please help!

--
Don't wait for your answer, click here: http://www.word.mvps.org/

Please reply in the group. Please do NOT email me unless I ask you to.

John McGhie, Microsoft MVP, Word and Word:Mac
Sydney, Australia. mailto:[email protected]