Date not formatted using "current layout formatting"

[bobfmpro]

I am having a perplexing problem exporting a date formatted according to the layout's formatting. My export script and it's formatting has worked perfectly for over 3 years.

I have been exporting from this layout with the formatting working perfectly - the problem is that I added another date field to be exported, formatted 2007-03-06 (for a MySQL database) - the formatting refuses to stick - the output file (tab separated) always shows this new field's data as 03/06/2007. There are other date fields on the layout that export properly - I have triple checked the fields definition parameters and the formatting - it is exactly the same as the other 3 date fields on the layout. I also tried using another date field with the same results. If I export the field manually (without the script) it works fine. This script is very large so I don't want to re-write it.

Has anyone experienced this problem? Is it possibly related to an upgrade from 8 to 8.5?

Thanks in advance.

[mon]

Yes, I am trying to format dates (or number, or text versions) in CCYYMMDD and am very very perplexed as well. My problem seems to stem from having started out from a date field all my months and days less than 10 show as single digit instead of two digit ( eg. 3 instead of 03).

I think after text conversion I will have write a logical calculation adding a preceding zero to single digit months and days.

[mon]

Oops! I find half my answer here -

http://www.filemaker.com/help/24-Editing%20objects15.html

Now I just have to make calculation fields changing the correctly formatted (with leading zeros) date fields to number or text fields.

Check here for your answer. I just didn't see those options in the date format. I kept looking for them in the Define Database Field area.

[MorFologist]

Something like this?

this is for a date within a text field to format as 00/00/00.

If there is a simpler way, someone do share.

Case(

Day(Date_Created)<10;

"0"&Day(Date_Created);

Day(Date_Created))

&"/"&

Case(

Month(Date_Created)<10;

"0"&Month(Date_Created);

Month (Date_Created))

&"/"&

Right(Year(Date_Created) ; 2 )

[comment]

You could do it this way:

Right ( "0" & Day ( YourDate ) ; 2 ) & "/" &

Right ( "0" & Month ( YourDate ) ; 2 ) & "/" &

Right ( Year ( YourDate ) ; 2 )

But I don't see why this would be required. It should be sufficient to custom-format the date on the layout, and check the "Apply current layout's data formatting..." option during export.

[Lee Smith]

I just tested a streight export of dates, using various formatting of todays date, and using the option to format the fields using the current layout formatting, they exported just fine.

Here is a sniplet of it.

"2007-3-14","3/14/07","2007-3-14","2007-14-3"

HTH

Lee

p.s.

I noticed that if you have the same date on the layout, formatted differently, it will only export one of them, and it was the last one placed that was exported in my test.

Edited March 15, 2007 by Guest
p.s.

[mon]

Thanks so much everyone!

Both Notaclone's and comment's calcs worked fine. I modified them to get to my required format by changing them like this:

Notaclone's calc -

Right(Year( CreationDate ) ; 4 )

&

Case(

Month( CreationDate )<10 ;

"0"&Month( CreationDate );

Month ( CreationDate ))

&

Case(

Day( CreationDate )<10;

"0"&Day( CreationDate ) ;

Day( CreationDate ))

and comment's calc -

Right ( Year ( CreationDate ) ; 4 ) &

Right ( "0" & Month ( CreationDate ) ; 2 ) &

Right ( "0" & Day ( CreationDate ) ; 2 )

The reason I specify the number of Right characters in Year is because elsewhere I need to use this same format but with a two character year ("070313") as well.

Lee's test explains why one may need to create a "pre-formated" field for an export.

Another related reason would be when embedding a merge field into a block of text.

Now I see I have to apply similar calculations for time elements but now it's a piece of cake; just a few more fields created to use for formatting.

Thanks again to Notaclone, comment and Lee!