Date Calculation

[davesand]

I, too, am fairly new to FileMaker, especially scripting. I have a FM database and have imported records from HyperCard, but some of the date fields have the wrong year. Instead of the year beign 2003 or 2004 it came in as 1903 and 1904. I'm trying to figure out a script that will change the year to the appropriate date, either 2003 or 2004.

At this point I'm pretty frustrated. Please help!

[stanley]

Dave:

Here's a brute force method. I've called your date field "Your_Date_Field".

Write a script with the following steps:

Show All Records

Go To Record (First)

Loop

If "Year(Your_Date_Field"=1903

Insert Calculated Result Select,"Your_Date_Field", "Your_Date_Field+36525"

End If

If "Year(Your_Date_Field"=1904

Insert Calculated Result Select,"Your_Date_Field", "Your_Date_Field+36525"

End If

Go To Record (Next, Exit After Last)

End Loop

This just adds 36525 days to any date from 1903 or 1904 - that's a century with leap years included. If you've got more problem years, you could have a single If statement with something like "<1905" to catch all the old years.

It ain't elegant, but I hope that helps

-Stanley

[jscooper]

You can also do a replace script step such as:

go to layout [any layout with date_field on it]

Replace Contents[Date_Field,date(month(Date_Field),Day(Date_Field),if(year(Date_Field)<1950,year(Date_Field)+100,year(date_field))]

go to layout [original layout]

Jeff

[davesand]

Stanley, thanks for the help. I'll try it and let you know how it works.

Dave

[davesand]

Jeff, Thanks for the help. I'll try this and let you know how it works.

Dave

[davesand]

Stanley, I tried your script and it doesn't seem to work. I think the reason is that the imported data was in a dd/mm/yy format instead of the dd/mm/yyyy format required by FileMaker. Any idea how to change that?

[stanley]

Dave:

If you set the date format to dd/mm/yyyy, then it comes out that you've got some that say 1903 or 1904, right? In which case it should work.

What kind of results are you getting from the script?

Actually, now that I look, I missed the closing parentheses on both Date() statements, in the lines that begin with "If" statements. The script should read:

Show All Records

Go To Record (First)

Loop

If "Year(Your_Date_Field")=1903

Insert Calculated Result Select,"Your_Date_Field", "Your_Date_Field+36525"

End If

If "Year(Your_Date_Field")=1904

Insert Calculated Result Select,"Your_Date_Field", "Your_Date_Field+36525"

End If

Go To Record (Next, Exit After Last)

End Loop

Now it should work.

-Stanley

[davesand]

Stanley,

It worked great! Thanks a bunch. Actually I had several fields with 1901, 1902, etc. I just changed the field names in the script and everything got changed the way I wanted.

Dave

[stanley]

Dave:

Glad to have been of help.

-Stanley

[Leb i Sol]

Does anyone have an answer to

Why these records are being imported in distorted format at all...?

BTW THANX Stanley!

[stanley]

Leb I Sol:

I think that Dave must have been using a fairly old HyperCard stack - I can't remember when Apple stopped developing HyperCard, but I'd imagine it was Y2K friendly (being an Apple product), but perhaps the stack just had two-digit years (or the dates were text? would that matter?) but nevertheless, he was coming in from HyperCard, and that's got to have something to do with it...

-Stanley