Problem converting dates

[PatriciaW]

I have changed date format (to European format of yyyy.mm.dd)when converting an application from FM6 to FM7. There were some dates which did not display correctly when I looked at them and they were those which had spaces between the elements.

So I wrote a script which looks for spaces in the 2 or 3rd position of the date field and replaces it with a slash "/".

The script runs and then converts the date but replaces the year with the current year. For example: 12 19 2001 becomes 12/19/2005.

What am I doing wrong?

[janedoe]

hi,

if you include your script, we can have a look for you.

thx,

jd.

[PatriciaW]

I fixed the script by explicitly using the original year (right 4 characters) and then concatenating it onto the middle of the original date ... but the script basically did the Set Field that I described.

Originally here is what I had for dates where the date was of this format "2 1 2001" or "2 12 2001" (i.e. the second character was a space):

Left(Performance::Performance Date;1) & "/" & Right(Performance::Performance Date; Length(Performance::Performance Date) - 2)

Here is the replace function that worked:

Left(Performance::Performance Date;1) & "/" & Middle ( Performance::Performance Date ; 3 ; Length(Performance::Performance Date) -7) & "/" & Right(Performance::Performance Date;4)

[comment]

Your first formula inserts only one slash, so for an input of "2 1 2001" the result is "2/1 2001" - not a valid date expression.

Since you are not reordering the date elements, you could have used simply:

Substitute ( Performance::Performance Date ; " " ; "/" )

[PatriciaW]

I realize that ... but it did replace both spaces in the date with a slash and then changed the year "2005". That is what threw me. (I was not aware of the substitute function at the time.) If it had left the last part of the date alone I would have known I did something wrong.

Thanks

[comment]

it did replace both spaces in the date with a slash and then changed the year "2005"

I don't think that is quite what happened here.

The result of your calculation is the TEXT string "2/1 2001".

When you use Set Field [ datefield ; textstring ], Filemaker tries to convert the text string into a valid date. The "2/1" part can be understood as a partial date entry in the current year. So the resulting DATE is Feb 1, 2005, and the rest of the string is simply ignored.

You will get the same result with Set Field [ datefield ; "2/1 anything" ].

[PatriciaW]

I accept what you say, BUT, it showed as 2/1/2005 (I guess ... I didn't notice at the time that the year was wrong. )

But I wanted to find out what I did wrong and now I know. I've changed the script accordingly.

Many thanks.