Please help: i have thousands of records with a formatted date field. I need to duplicate this field and its content for all records and then change the date format of the new fields, so i have two date fields with the same date values, but in different formats.

After that, all entered date in the first fields should automatically appear in the new date field with the new format. Is this possible? Thanks.

Hello Eyal

I do not know why you need to have 2 fields with the same info in them. If it is for display purposes then are you aware that you can format a date field to display in many different formats.

Assuming this is a date field go into layout mode, select the date field that you wish to format and use the format menu - date. That way you can display the same date from the same field any number of ways

HTH

Phil

Hi Phil,

Thanks for the reply. I need two different formats because one field ("3 January 06") is then exported for listings in a magazine and the other ("20060103") will be used for an online listings database. So i need one field which is easy to read, and one which SQL will understand.

I am afraid that I am not qualified to comment on exporting to SQL but as far as I am aware FM stores ALL date fields in the same format (the current system format) and then changes the format to suit the users requirements hence I still think (although I may be wrong) that you will only need 1 date field.

One way around this problem would be to set your system format to be that required by SQL and then display the same field in easy to read mode as above but I am sure someone will have a better solution for you. I am the one who usually has the dunces hat on around here .

Regards

Phil

Actually, Filemaker stores dates as serial numbers (today is 732506). I think the easy way here would be to define a calculation field with a TEXT result =

Year ( Datefield ) & Month (Datefield) & Day (Datefield )

You are right - and if you want a YYYYMMDD format than this is the calculated field you need (just figured it out)

Year(Date) &

If(Length(Month(Date)) =1, "0" & Month(Date), Month(Date)) &

If(Length(Day(Date)) =1, "0" & Day(Date), Day(Date))

Oops - missed that. There's a shortcut coverall for this:

Year ( Datefield ) & Right ( "00" & Month ( Datefield ) ; 2 ) & Right ( "00" & Day ( Datefield ) ; 2 )

(A single "0" would do just as well in this case, but I like to be reminded what the expression is doing there.)