get date from text

[saralee]

Hi, I need to extract some text in the date format. For example in my text field as below, every line looks like this, starting with a date part which does not have consistent format.

3/09/2009 - text text more text here...

4/9/08 - etc etc

The year part sometimes is in 2-digit format and sometimes is in 4-digit format. I would like to have the format set as MM/DD/YYYY in the final product. I've already set a date field comment_date and plan to extract the date part from the comment box to this field. Wonder if anyone has a good idea on how to do so?

My current method uses GetDate() function but does not return valid output for some dates..

[comment]

Try =

GetAsDate ( LeftWords ( Textfield ; 1 ) )

[Raybaudi]

Note that dates with year in 2-digit format will be translated so:

from 00 to 40 they become years of XXI century ( 2000 - 2040 )

from 41 to 99 they become years of XX century ( 1941 - 1999 )

[saralee]

thanks. I tried it but some text was converted to "?" ...

i wonder why...

[comment]

An example or two would be useful - but they either don't have a date as the first word, or the date is invalid (e.g. 13/13/99).

[Raybaudi]

The date must be a valid date... if the text is:

2/29/06 - etc etc

you'll have an error ( ? )

[saralee]

the examples are:

both of these won't work. they will show as "?" after the getasdate function

12/20/2001

02/26/2002

but these work

4/06/2002

10/10/2001

I don't see any pattern here...

[Raybaudi]

The DB was created with a system that use the dd/mm/yyyy date format

[saralee]

I figured the GetAsDate funtion is properly only recognizing the dates that could be converted in the DD/MM/YYYY format, so dates like 01/13/2010 could not be recognized. I remember I accidently clicked the "revert field" setting at some point when a dialog box asked me to change the date input format. Is that related?

Also, the time setting at my OS is MM/DD/YYYY. I changed it from another setting before.

[comment]

First, set the file to always use the system settings (or have it ask what to do) in File Options. Then repeat the process, because the way you have it now, 4/06/2002 was recognized as June 4th 2002 (you can verify this by formatting the field to display the month as text).

To cure this problem permanently, I'd recommend you save a copy of the file as clone (no records) and import your data into it.

[saralee]

The file option's setting is currently set as "always use the current system setting" i don't think there has been a change before..

Did you mean I need to import this date related file as clone? so that would be after running the script to extract the date part? What did you mean by permanently curing the problem? Even after I set the right setting, the same issue would come up again? thanks!

[comment]

I am sorry - I keep forgetting there's a bug in the GetAsDate() function that prevents it from working correctly in this case. Since your file was created under a system set to d/m/y, and you need to interpret text dates entered as m/d/y, you must convert your file first. This means save a copy of your file as a clone, import the data from the old file and then run your script.

Note that this is quite an operation (how did you get into this situation in the first place?). You must be careful to keep the existing serial numbers (don't perform auto-entry when importing) and check the next serial number in every table after importing.

[saralee]

Thanks, comment.

I recall at first dates newly entered were in dd/mm/yyyy format.. actually i forgot how I converted all of them into mm/dd/yyy format along with the OS time setting.. anyways, now the db is still keeping the "old memory" as to recognize the date in dd/mm/yyyy format..

I will follow your suggestion to save a clone and migrate the data again.. do you mean I need to migrate all date field related tables or just this specific table that I have trouble with.. my date fields are fine with other tables.

Thanks a lot!

[comment]

You need to migrate everything. There is no way to do it partially.

my date fields are fine with other tables

Well, the "use system setting" does work for them. But if you want to be sure you won't have other problems, wipe the slate clean.

Edited August 17, 2010 by Guest

[saralee]

btw, why i shouldn't perform auto-enter while importing? is it to prevent screwing up the ID numbers?

This date field is in my tblComments and I actually don't have a unique identifer for it.. I have defined babyID and householdID to identify each comment.. I hope this is ok..

[comment]

If you auto-enter the serials, and the original has a deleted record (for example), Filemaker will renumber your records and child records will be shifted to another parent.

[saralee]

ok, i'd better migrate the whole thing again.. after i discovered the problem, i added the script "getasdate" function, and did re-run the script couple of times.. but it still didn't work.. i hope re do everything over shall work this time...