Date Puzzler

[stanley]

Hello All:

This is a puzzle caused by the way FMP internally formats dates. If you're at all conversant with the issue, you know that FMP's default format for all dates is based on the date format on the machine where the file in question was first created. For those of us in the US, that means that for almost all of our files, FMP is internally formatting the dates as mm/dd/yyyy.

Now, FMP will display the dates in whatever the current system format is, so for the most part you never have to worry about the internal formatting. And the actual date value is based on the number of days since 1 January, Year 1 CE (or 1/1/0001).

Now that we have the introduction out of the way, let me explain what the problem is. We have a large FMP system (currently running on multiple instances of Server 9 and 10) which is used around the world. These systems communicate with lots of other systems, also based all around the world, which are of different sorts - SAP, Oracle, SQL, and so-on. While developing an EDI between an SAP system and our FMP system, I have discovered that I sometimes get dates which are formatted backwards (i.e., mm/dd/yyyy versus dd/mm/yyyy) because although I know the format of the data being brought in, I do not know the format of the computer that is running the script in question. The data is being brought in via a raw xml file, which is imported into a text field and then parsed, so the value (let's call it $$date) is being put into the date field (let's call it DateField, and it is indeed a date field) via a SetField step. Simple enough. Except the data in $$date is always in the format dd/mm/yyyy, and if the date is, say, 26/07/2010 and the computer is set to US date format, then the result is that DateField, of course, gets the value '?'. Because there's no 26th month of the year. If the computer is set to European dd/mm/yyyy format, this problem goes away completely, but not all of our clients are in Europe.

My original solution was to have a globally stored value for each of our files, saying what the national format was (dmy or mdy), and although that might cover 80% of usage, the files are accessed from across borders (there are US colleagues who might access any of the files, for example) so that solution is actually no good.

I decided to try to detect the formatting of the currently active computer (the one calling the script) so I could know whether to flip the day & month around in my SetField statement, and that's when things got weird. In my example, where the dd/mm/yyyy date I am using is coming in as the text string '26/07/2010', which I store as $$date, I get the following values:

$$date = '26/07/2010'

GetAsText($$date) = '07/26/2010'

Left($$date;2) = '7/'

I could bore you with all the iterations of this I have tried (including GetAsDate(), GetAsNumber() and everything else I could think of, in every combination I could dream up. But I won't punish you, because I'm asking for your help.

What I had wanted to do was to detect that leading 26, which would tell me the date was mdy via a calculation like: If( Left($$date;2) <> Left($$date+1;2) AND Left($$date;2) <> Left ($$date-1;2) ; "dmy" ; "mdy"). However, regardless of the date settings of my machine, the above values do not change, so I always detect '7/' for the first two characters. That's because FMP is secretly converting the date value in $$date to a text value, using its hidden formatting, and thus is always putting the month first. Because the file was created on a US-format machine.

My question to you is how to detect the date format of the current machine. With this information I can then know how to format the known date value when using SetField.

Sorry for being so long-winded, and I thank you all in advance.

Stanley

[Raybaudi]

What about:

If ( Left ( GetAsDate ( 365 ) ; 2 ) > 12 ; "dmy" ; "mdy" )

[comment]

I believe there's a bug in the GetAsText ( date ) function: the format returned is always in the setting saved with the file - even if you choose to use the current system's settings.

[stanley]

That's right. That's what causes the problem, Comment. If I try to detect the Left(x;2) value, I will always get the month because this file was created in the US. But I may actually be on a computer with System settings for Europe, so the day value should go in that slot. It's frustrating.

[stanley]

Hi Ray:

The problem with your calc is that it assumes that the 'day' value could be found via Left(x;2). But it can't in this case because the date fields are always resolving as mm/dd/yyyy because that was the format of the system the file was created on. The system of the client is not taken into account when parsing/analyzing a date field, but it IS taken into account when entering a date field...

[comment]

One possible solution is to bring in the date as a serial number and use GetAsDate ( number ).

[comment]

Another way: export anything as XML and peek at the DATEFORMAT attribute of the DATABASE element.

[stanley]

Hi Comment:

I'm not sure I follow that. If I bring in today's date (27 July 2010) as $$date and I do this:

GetAsNumber($$date)

I get 733980 regardless of being on mdy or dmy system settings. Okay. But is there a way to enter that value and have FMP turn that into a valid date in the current system's format? In other words, a way to get that entered in as 27/7/10 on a dmy system or 7/27/10 for mdy?

Thanks

Stanley

[stanley]

Comment Said:

"Another way: export anything as XML and peek at the DATEFORMAT attribute of the DATABASE element."

Oh, now we might be on to something!

[Raybaudi]

Get ( SystemLanguage ) could give a clue

[stanley]

Aha! That's a step in the right direction.

When a record is exported to xml, the actual system settings for the date are tagged, as follows:

DATEFORMAT="M/d/yyyy"

or

DATEFORMAT="D/m/yyyy"

Well done, Comment! Although it'll be a lot of work to implement, at least it is a way to solve my problem.

Certainly deserving of a beer at DevCon!

Thanks

Stanley

[stanley]

Get(SystemLanguage) doesn't work for us. 75% of the time it will be 'English' (and anyway, English (US) is mm/dd/yyyy and English (UK) is dd/mm/yyyy.)

Thanks for the suggestion, though.

[comment]

it'll be a lot of work to implement

I don't think it's that difficult. But if you want to spend about an hour or two on it, you could have a stylesheet for the export producing the stylesheet for the import.

Re my earlier suggestion: I meant something like the attached. I don't think there's a way to do it directly into the date field, but I'd love to be proven wrong.

numtodate.zip

[Vaughan]

Here is a little calc I've been using to display information in dialogs:

Substitute( GetAsText( Date( 11 ; 22 ; 3333 ) ) ; [ "1" ; "m" ] ; [ "2" ; "d" ] ; [ "3" ; "y" ] )

[comment]

The problem with the calculation is that it returns a wrong result when Use System Formats is on.

[stanley]

Oh yeah. Basic implementation's not a problem. Fitting it into our solution (which is extremely convoluted) will take a bit of labor, but not that big a deal, really.

Thanks to all, and I'll post how it turns out...

Stanley