ODBC Import Problem with DateTime

[presley2]

Hi,
I have a MySQL table on a MacOSX 10.4 server and Filemaker Pro Server 7. I am using the "Actual Technologies" ODBC driver for this project. I have a number of fields I am trying to import from MySQL into a client FM DB.

The Problem:
Two of the MySQL fields are formatted as DATETIME and they serve as a creation and update AUTO-ENTER fields using the NOW() MySQL function. The fields are auto filled with a string that looks like this 20060125132456 (yr, m, d, hr, sec) ....when using the FM ODBC importer these two fields import and look like this 365: 12: 23 .....all other import fields work perfectly..I have tried changing around FM field types...nothing seems to work ...I would like it if the fields were imported either as a text string (just like MySQL) or with FM DateTime formatting...

Is there an EZ solution?
Is there a workaround?
Is there a product I can buy (ODBC) that will greatly simplify communication back/forth between FM and MySQL and will resolve these field type differences automatically?

Thanks,
Jim E

 

[lebisol]

if you predefine your field they should be formated as text
it has been a while...but I belive you can 'un-compund' the text field by Count/trim
so theory would be:

fulldate_imported=20060125132456
yrs_calculated=Count/trim 4 first characters
month_calcualted=Count/trim 2 after 4

etc.
sorry I don't remmeber exact function of how FM "counts/trims" from left to right
let me know how it works out...maybe a good excuse for me to load FM again
All the best!


:--------------------------------------------------------------------------:

All around in my home town,
They tryin' to track me down...

 

[JeanW]

It's parsing.

If your datestamp field is text, with always the same amount of characters in the same order, try something along these lines

Datestamp (text) = 20050125131532
year = calc result nr: Left(datestamp;4) -> result 2005
month = calc result nr: Middle(datestamp;5;2) -> 01
day = calc result nr:Middle(datestamp;7;2) -> 25
hour = calc result nr:Middle(datestamp;9;2) -> 13
min = calc result nr:Middle(datestamp;11;2) -> 15
sec = calc result nr:Right(datestamp;2) -> 32

From this point on you can start with combinations to rebuilt the date and time in FM readable values.

HTH