unusal(?) date conversion problem

[deke]

I created a new db (v7) with date fields defined as date-type. The old db had all 'date' fields as text fields: "yy/mm/dd" The new db date should be "mm/dd/yyyy"

I have tried several approaches, but have not been successful in converting the date-oriented fields from the old to the new db.

I have looked at other similar entries in this forum, but don't seem to be able to apply those to this situation. (My fault, likely.)

Can someone help with specific scripting, if that is what is needed?

Thanks.

Deke

[RalphL]

You will need to conver the text field into a date field. The calculation is Date (Left (oldDate; 2); Middle (oldDate; 4; 2); "20" & Right (oldDate; 2))

This assumes that the old dates were all 2000 +

If you have dates from 1900's yuo can try this:

Date (Left (oldDate; 2); Middle (oldDate; 4; 2); Case (Right (oldDate; 2) > 10 ; "19" ; "20") & Right (oldDate; 2))

Edited August 20, 2005 by Guest
Typos

[deke]

Although I do know about calculated fields, I just don't seem to understand the process of converting the date field...

Do I create a new date-type field in the old db corresponding to text-type "oldDate"?

Do I populate the new (real) date field in the old db?

How do I go about doing the data population?

Sorry for these dumb questions, but I seem to be missing the sequence here.

Thanks.

[RalphL]

The calculation should work in either file. In an older US version use comma instead of semicolon.

In either case have a field to hold the old date and a field to hold the new date calculation.

It might be cleaner to do this in the old file and just import the correct date.

[deke]

I have the old db with an added "date" field that is defined as auto-enter/calculated value with the Specify Calculation=

datepaidnew="followed by your formula."

My question at this point is: how do I get the datepaidnew field populated? what action does that?

[CobaltSky]

Hi Deke,

First of all, Ralph was heading in the right direction, however since you've said that your old dates are in a yy/mm/dd format - and since the Date( ) function syntax is Date(month; day; year), you'll need a conversion formula more along the lines of:

Date(Middle(oldDate; 4; 2); Right(oldDate; 2); (19 + (Left(oldDate; 2) < 10)) & (Left(oldDate; 2))

This formula will convert your yy/mm/dd text values into FileMaker's internal date format suitable to be stored in a date field rather than a text field.

As regards the procedure, there are several options. However I would suggest that you create a new date field in the old field, then, in Browse mode (in the old file) place the cursor into the new date field and select the Replace Field Contents... command from the Records menu. In the resulting dialog, choose the option for Replace with calculated result: and enter a formula like the one above.

Once you complete this process, check carefully through the records in Browse mode to ensure that the dates in the old and new fields match. Once you're satisfied that this is the case, you will be able to import the data into your new file, importing the dates from the new date field into a FileMaker Date field in the new file. B)

[deke]

Ray, Your approach and formula seemt o work okay, however, the yyyy after the Replace Field Content is "0004" rather than "2004"

What needs to be added to your formual to give the date the right centruy?

Thanks, much for the help.

[RalphL]

Ray's caalculation needs another ) at the end.

I messed up on the format of your original date.

What Ray did is use a Boolean in his calculation that evauates to 1 if true and 0 if false. Left(oldDate, 2 ) < 10

[deke]

I put in the "extra" ")" and it still gives a yyyy value as "0004"

For this set of records, the only year contained in the olddate text field is, "04" as in yy/mm/dd.

Any ideas why the "replace field contents" result shows the calculated date as, 03/11/0004", for example and not 03/11/2004?

Also, for future reference, why does the calculation include the check for "<10"? I know I had tried the Date function before and got it to work on all dates with month <10.

Thanks.

[comment]

I think Ray meant:

Date (

Middle ( oldDate ; 4 ; 2 ) ;

Right ( oldDate ; 2 ) ;

19 + ( Left ( oldDate ; 2 ) < 10 ) & Left ( oldDate ; 2 )

)

The check for < 10 is based on the assumption that year "09" means 2009, and year "10" means 1910.

If you don't have dates from the previous century, you can make it simply:

Date (

Middle ( oldDate ; 4 ; 2 ) ;

Right ( oldDate ; 2 ) ;

2000 + Left ( oldDate ; 2 )

)

[RalphL]

Date(Middle(OldDate; 4; 2); Right(OldDate; 2); (19 + (Left(OldDate; 2) < 10)) & (Left(OldDate; 2)))

The first term is the month which is the middle of the OldDate.

The second term is the day which was the last term of the OldDate.

The third term is the year it assums that the year is between 1910 and 2009. So the <10 means that if the year is 2000 to 2009 the calculation will be true and adds a 1 to 19 for 20, other wise the value is 19. This numbe is then concatenated with the year from the OldDate to give a 4 digit year.

I don't think I would use replace since the OldDate is text and the new one is date. I would use a new calculated field for this. Then import the calculated field.

[deke]

Thanks everybody for the help. I am able to do the date comversion from text to date correctly.

The help is very much appreciated!!

Deke