Convert FM Date field to MySQL timestamp(8)

[MattPage]

Hi,

I am exporting data to a MySQL database from ODBC but date formats are causing a headache..

FM Date = 3/2/2006

MySQL Timestamp(8) = 20060302

Is there an easy to convert from FM to MySQL date format. At the moment the export script goes something like ....

 Set Variable [$startDateDay; Value:Day(Events:event_startDate)]

 Set Variable [$startDateMonth; Value:Month(Events:event_startDate)]

 Set Variable [$startDateYear; Value:Year(Events:event_startDate)]



 If[$startDateDay < 10]

     Set Variable[$startDateDay; Value: "0" & $startDateDay

 End If



 If[$startDateMonth < 10]

     Set Variable[$startDateMonth; Value: "0" & $startDateMonth

 End If



 Set Variable [ $startDateMySQL; Value:$startDateYear & $startDateMonth & $startDateDay ]

But when I have 15 different dates in the table it becomes very annoying to write all the scripts...

Any suggestions?

Thanks

Matt

[Raybaudi]

You don't need a ( 15 ?) script, but you need to export a calculated text field (15 calc fields) with calc:

Year(date) & right("0" & month(date);2) & right("0" & day(date);2)

[MattPage]

Thanks for the response, think I need a book on FM scripts....

I have however come up with a custom function which I can just call while doing the Execute SQL script step...

/* fmDate_to_MySQL_Timestamp8(fmDate) */

/* Convert a Filemaker DATE field to a MySQL Timestamp(8) field */



Let([

	fmDay = If(Length(Day(fmDate)) < 2; "0" & Day(fmDate); Day(fmDate) );

	fmMonth = If(Length(Month(fmDate)) < 2; "0" & Month(fmDate); Month(fmDate));

	fmYear = Year(fmDate)

];

Case( 1 = 1; fmYear & fmMonth & fmDay; fmYear & fmMonth & fmDay))

Will look at the Right function, I was trying to find that earlier but kept searching for Pad or something....

[Raybaudi]

This custom does the same calculation...with a little more complexity !

[Lee Smith]

Hi Matt,

You need to update your profile to reflect that you have [color:blue] Advance , as this can make a difference it the responses we can give.

Lee

Edited February 16, 2006 by Guest

[MattPage]

Thanks.

Profile updated.

raybaudi, couldn't see your function anywhere...

[Raybaudi]

Sorry...

I had to say :"that" custom (the custom function that you showed us)