Jump to content

Convert FM Date field to MySQL timestamp(8)


MattPage
 Share

This topic is 5694 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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....

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

This topic is 5694 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.