Filamaker 5.5 / xp pro
using a text field I use metric date in it: 2005_02_25 and I can add the 
letter c in the field to indicate when I have Circa dates without 
interferring when I want to sort them in chronological order. My problem is 
that thousand of records have been imported in the database with the 
mm/dd/yyyy format. In addition numbers 00 have been put when teither the 
date or the month is unknown. In metric I would only put i.e 2005 02 if the 
day is unknown but they have used 02/00/2005.How can I change those date in 
a metric format? I would need to convert those zeros by nothing. Is there a 
script allowing me to do this?

Please help. 

In article <xLRTd.59262$6U2.1382294@weber.videotron.net>, "Simon Dupuy"
<sammfm@videotron.ca> wrote:

> Filamaker 5.5 / xp pro
> using a text field I use metric date in it: 2005_02_25 and I can add the 
> letter c in the field to indicate when I have Circa dates without 
> interferring when I want to sort them in chronological order. My problem is 
> that thousand of records have been imported in the database with the 
> mm/dd/yyyy format. In addition numbers 00 have been put when teither the 
> date or the month is unknown. In metric I would only put i.e 2005 02 if the 
> day is unknown but they have used 02/00/2005.How can I change those date in 
> a metric format? I would need to convert those zeros by nothing. Is there a 
> script allowing me to do this?
> 
> Please help. 

"Metric Dates"?!? That's a new term on me.  :o)

Anyway, you say the records have been imported, but you didn't say
where that imported date has been stored - is it already in the Metric
Date Text field or is it in a separate Date field?? You also don't say
what should happen if the imported month is "00".

The first question will make quite a difference to the commands needed,
ie. whether to use date functions like Month() or text functions like
Left().


Helpful Harry                   
Hopefully helping harassed humans happily handle handiwork hardships  ;o)

The dates (mm/dd/yyyy)have been imported into the Date Text field. If the 
month is 00, there should be only the year left in the field.

thanks for the help.

"Helpful Harry" <helpful_harry@nom.de.plume.com> a �crit dans le message de 
news: 260220051807518825%helpful_harry@nom.de.plume.com...
> In article <xLRTd.59262$6U2.1382294@weber.videotron.net>, "Simon Dupuy"
> <sammfm@videotron.ca> wrote:
>
>> Filamaker 5.5 / xp pro
>> using a text field I use metric date in it: 2005_02_25 and I can add the
>> letter c in the field to indicate when I have Circa dates without
>> interferring when I want to sort them in chronological order. My problem 
>> is
>> that thousand of records have been imported in the database with the
>> mm/dd/yyyy format. In addition numbers 00 have been put when teither the
>> date or the month is unknown. In metric I would only put i.e 2005 02 if 
>> the
>> day is unknown but they have used 02/00/2005.How can I change those date 
>> in
>> a metric format? I would need to convert those zeros by nothing. Is there 
>> a
>> script allowing me to do this?
>>
>> Please help.
>
> "Metric Dates"?!? That's a new term on me.  :o)
>
> Anyway, you say the records have been imported, but you didn't say
> where that imported date has been stored - is it already in the Metric
> Date Text field or is it in a separate Date field?? You also don't say
> what should happen if the imported month is "00".
>
> The first question will make quite a difference to the commands needed,
> ie. whether to use date functions like Month() or text functions like
> Left().
>
>
> Helpful Harry
> Hopefully helping harassed humans happily handle handiwork hardships  ;o) 

> In article <xLRTd.59262$6U2.1382294@weber.videotron.net>, "Simon Dupuy"
> <sammfm@videotron.ca> wrote:
> 
> Filamaker 5.5 / xp pro
> using a text field I use metric date in it: 2005_02_25 and I can add the
> letter c in the field to indicate when I have Circa dates without
> interferring when I want to sort them in chronological order. My problem 
> is that thousand of records have been imported in the database with the
> mm/dd/yyyy format. In addition numbers 00 have been put when teither the
> date or the month is unknown. In metric I would only put i.e 2005 02 if 
> the day is unknown but they have used 02/00/2005.How can I change those 
> date in a metric format? I would need to convert those zeros by nothing. 
> Is there a script allowing me to do this?
> 
> The dates (mm/dd/yyyy)have been imported into the Date Text field. 
> If the month is 00, there should be only the year left in the field.

Since all the dates are in the format mm/dd/yyyy, then it's a simple
case of using the Left, Middle and Right functions to extract the data
and rearrange it in the order you want.

If your field is called TextDate, then the basic formula (ignoring the
00's for a minute) is:

     Right(TextDate, 4) 
          & "_" & Left(TextDate, 2) 
          & "_" & Middle(TextDate, 4, 2)

This takes the four rightmost characters (the year) adds a "_" then
adds the two leftmost characters (the month), adds another "_" and
finally adds the two middle characters starting with the fourth one in
(the day).

This assumes that there is ALWAYS two digits for the month and day and
four digits for the year - if not, then we would need to use the
Position function to find the two "/" characters that divide the date
into parts.


To get around the "00" day digits we replace the Day part of the above
calculation (plus the preceeding "_") with an If function:

      "_" & Middle(TextDate, 4, 2)    

now becomes

      If (Middle(TextDate, 4, 2) = "00", "", "_" & 
                                  Middle(TextDate, 4, 2))

This tests the day digits and if they are "00" then "" (ie. nothing) is
added to the final date, otherwise the preceeding "_" and two digits
are added.


In a similar way we can test the month digits with another If function:

     "_" & Left(TextDate, 2)   

now becomes

     If (Left(TextDate, 2) = "00", "", "_" & Left(TextDate, 2))



With both of these refinements in place you get a function which will
handle dates with "00" for day, dates with "00" for month, and dates 
with "00" for both day and month.

     Right(TextDate, 4) 
          & If (Middle(TextDate, 4, 2) = "00", "", "_" & 
                                  Middle(TextDate, 4, 2)) 
          & If (Left(TextDate, 2) = "00", "", "_" & Left(TextDate, 2))


This basic formula could be used in a script or via the Replace command
in the Records menu (be careful with either - ** MAKE A BACKUP ** of
the file first).

A script would have to loop through all the records replacing the
imported date with the reformatted one. Something along the lines of:

     Go To Recored / Request / Page [First]
     Loop
        Set Field [TextDate, 
                   Right(TextDate, 4) 
                   & If (Middle(TextDate, 4, 2) = "00", "", "_" & 
                                  Middle(TextDate, 4, 2)) 
                   & If (Left(TextDate, 2) = "00", "", "_" & 
                                  Left(TextDate, 2))]
        Go To Record / Request / Page [Next, Exit After Last]
     End Loop

This would only go through the records in the Found set, so you'd need
to Find the records you want to reformat before running the script.


        

Helpful Harry                   
Hopefully helping harassed humans happily handle handiwork hardships  ;o)

On Fri, 25 Feb 2005 22:09:13 -0500, Simon Dupuy wrote:
>  Filamaker 5.5 / xp pro
>  using a text field I use metric date in it: 2005_02_25 

That's not metric. ISO recommends 2005-02-25.

>  and I can add the 
>  letter c in the field to indicate when I have Circa dates without 
>  interferring when I want to sort them in chronological order.

You can do so - but not in a date field. You have to take a text-only
field. A good choice where you can enter much more variations, such as

2004 - 2006
2004 / 2005
2005-02
2005-Q1
2005-W09

etc.


>  My problem is 
>  that thousand of records have been imported in the database with the 
>  mm/dd/yyyy format. In addition numbers 00 have been put when teither the 
>  date or the month is unknown. In metric I would only put i.e 2005 02 if the 
>  day is unknown but they have used 02/00/2005.How can I change those date in 
>  a metric format? I would need to convert those zeros by nothing. Is there a 
>  script allowing me to do this?

Is it a real date from a date field. Then all you have to do is to
change the date DISPLAY format in order to show the 'metric date'.

However, I guess that you want to convert one format to the other.

Example:

Case(
patterncount(textdate, "/") = 2,
 case(length(middlewords(textdate, 3,1)=2 and
      left(middlewords(textdate, 3,1),1)="0",
      "20" & middlewords(textdate, 3,1),
      length(middlewords(textdate, 3,1)=2,
      "19" & middlewords(textdate, 3,1),
      length(middlewords(textdate, 3,1)=4,
      middlewords(textdate, 3,1),
      "??"& middlewords(textdate, 3,1))
 & "-" &
 right("0"&middlewords(textdate, 2,1),2) 
 & "-" &
 right("0"&middlewords(textdate, 1,1),2),

textdate)

You have to get the matching part of the original format (I assumed
it's in a field textdate) and reorganize it with leading zeroes.

I've added some extra verification in order to change from YY to YYYY.
You may provide more and more conversion methods as you like, e.g.
converting "2 Feb 2005", too. 

For simplification I used word operations on textdate as is. Maybe you
have to split the text to words first, e.g. by using
"substitute(textdate, "/", " ")" instead of textdate.

Give it a try,
Martin