Need Help Quick: Date Conversions

[deke]

I imported a few thousand records from FM5.5 into FM7 that had a date field defined (in the 5.5 file) as a text field in the following format: YY/MM/DD. That same field is properly defined in the FM7 file as a Date field.

I need a script that now converts the YY/MM/DD Date field data in the FM7 file to that which is compatible with FM7 Date format MM/DD/YYYY.

1. All records are for 2004.

2. I plan to use the script to "Replace Contents" of the Date field in FM7.

I could use some help really quickly, if possible. Thanks, Deke.

[LaRetta]

You might try:

Middle ( textDate ; 4 ; 4 ) & "/20" & Left ( textDate ; 2 )

or even

Right ( textDate ; 5 ) & "/20" & Left ( textDate ; 2 )

[IdealData]

How did you import from 5.5 to 7 ? Did you mean you exported from 5.5 (via csv etc) then imported to 7 ?

LaRetta's right, but you might need to check out the GetAsDate function.

GetAsDate (Middle ( textDate ; 4 ; 4 ) & "/20" & Left(text ; 2))

[LaRetta]

Hi Mark,

vs. 7 allows text to set a date field. GetAsDate() isn't required in this operation. It replaces InsertCalculatedResult() (required by prior versions to include text in date calcs) 'as is' just fine and works perfectly in Set Field[] as well as Replace Contents.

L

[deke]

Thanks for the quick response.

I'm not sure I explained the situation well enough, sao let me add the following. Y'all may already have taken this in account.

1. I imported a text-defined "date" field from 5.5 to a date-defined date field in 7.

2. I want to "Replace Contents" of the existing (bad) imported date field 'on the fly' using the script.

Thanks.

Do I use the script just as is or is there a function name that preceeds the suggested script.

[deke]

I ran the script "Middle(....) & "/20"..... and when I view as table, I see nothing but "?" in the Date field I did the Replace Contents on.

When I click on the data field in the View As Form, I see "0/20/204/" in one of the records, "/201/204/", and "1/20/204/", "/200/205/" others, as examples.

[LaRetta]

Then your sample data isn't truly as described, ie: YY/MM/DD but rather YY/M/D.

I almost asked for verification on whether it contained leading zeros on the month/day.

[comment]

I believe the formula should be:

Middle ( textDate ; 4 ; 5 ) & "/20" & Left ( textDate ; 2 )

This indeed pre-supposes leading zeros. To play it safe, I would go with:

Let (

txt = Substitute ( textDate ; "/" ; " " )

;

Date (

MiddleWords ( txt ; 2 ; 1 ) ;

RightWords ( txt ; 1 ) ;

2004

)

)

[LaRetta]

I had a typo in my first calc:

Middle ( textDate ; 4 ; 5 ) & "/20" & Left ( textDate ; 2 )

I deeply apologize for the error ... :crazy2:

Edited September 16, 2005 by Guest

[deke]

Sorry, LaRetta, that didn't work either. I still think I am not communicating the scenario like I should.

The FM7 file contains a db-defined Date field. The data for this field was imported from a 5.5 file that had the field defined as text in the format, YY/MM/DD, with leading zeros.

As we know, FM7 assumes a YYYY year and, I suspect, makes that adjust in the date field in question.

How can I send you/tell you what the raw data looks like in the FM7 file, if that is what you need. Do you need to know how the old and new date fields are formatted?

Thanks for your patience.

Deke

[deke]

As an fyi, we are not dealing with a text field at all in the FM7 file. We are trying to convert a bad date-defined field that was imported from a 5.5 text-defined field, that was used to simulate a Date field).

Hope this helps.

[comment]

To be safe, you should re-import the dates into a text field. Because a date like March 10, 2004 is "04/03/10" in YY/MM/DD. Filemaker will cheerfully accept this as a valid date of April 3, 2010 in MM/DD/YY format.

[deke]

That would be a very good approach, however, the records that were imported have now been updated, in some cases, in the FM7 file.

So, I can't go back and re-import w/o losing those updates.

Yuk, what a nasty problem!

Any other suggestions?

[comment]

Sure you can. Just do this on a copy of the file, until you're sure it has worked properly.

Import the same source again, but this time select "Update matching records in found set" instead of "Add new records". Select your ID field as the match field, and the new textDate field as the ONLY target field.

[deke]

Okay. So...I will need to add a text field in the FM7 file for the incoming txtDate and then do the import on a "match-only." Doing this will bring in the old/bad text-defined date field into the FM7 file (a text-defined field as a temp storage place).

In the FM7 file I then run the script ("Replace Contents" in the Date-defined field) to convert the text-defined date field to one that is compatible with FM7's Date format.

Before I run the script I will have two fields, (1) Text field with YY/MM/DD format and (2) Date field that has previously-imported YY/MM/DD that FileMaker made into a Date field.

Do these sound like the right steps?

[comment]

Yes. But I stress again: since neither Import nor Replace Field Contents are undoable, have a backup.

[deke]

Thanks for the advice. I have gone back to the original source records and have done the "match only" on the Import.

That worked fine.

Again, yuk, on having to mess with bad data when upgrading to a different db.

Thanks, once again. Deke

[LaRetta]

I wonder how/if FM accounted for the change in "/" and "-" as word breaks during version migration, ie, they don't break words if between numbers in vs. 7. Calculations and scripts relying on these word counts would all produce inaccurate results, right? I haven't heard much discussion about it so I suppose FM 'read' every calc and script and adjusted them accordingly?

In some ways, the change has made life easier, working with dates particularly; but it's taking some getting used to. Are these the only two characters that have changed their 'behavior' or does it affect them all? Obviously the space is still a word break. :wink2:

[comment]

Hm... I thought I have made a pretty good list here, but obviously I missed that the slash and the hyphen also have their own special rule:

"one-two" = 2 words;

"1-two" = 2 words;

"one-2" = 2 words;

"1-2" = 1 word.

This makes three special rules so far:

(1) the period;

(2) the slash and the hyphen;

(3) the single quote.

I wonder if there are more rules/characters that I have missed.