If you can load the data into MySQL, put the date field into a CHAR(10)
field. After the data load, add a date field using ALTER TABLE. You
should then be able to run something like
UPDATE TABLE tablename SET NewDate
= CONCAT(
RIGHT(OrigDate,4),"-",
MID(OrigDate,4,2),"-",
LEFT(OrigDate,2)
);
You can then drop the original date column with alter table. If there
are more than 1 date fields just follow the same procedure for each one.
If there are several date fields in the converted database, you may want
to use an editor to build the multiple Alter/Update/Alter sequencies,
save as changedate.sql and then execute using
MYSQL database name < changedate.sql
This lets you use copy paste to generate the multiple SQL statements
with minimal typing.
-------------------------
Another process which works if you have Microsoft Access is to
Import the tables into an Access Database
Create the tables in MySQL
Link the MySQL tables into the Access database through an ODBC
DSN
Build an APPEND query to copy the Access tables into the MySQL
tables
Delete the Access database after verifying success
Moving the dates through the ODBC interface automatically converts the
DD-MM-YYYY format to the YYYY-MM-DD format used by MySQL.
Gordon Bruce
Interstate Software
US MySQL Training Partner
> -----Original Message-----
> From: Cameron Murdoch [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, May 29, 2002 5:46 PM
> To: [EMAIL PROTECTED]
> Subject: Filemaker Pro and Dates
>
> Hi all,
>
> I apologise if this question has been asked recently; I have checked
the
> list archives and could not see anything. This is my problem:
>
> I am trying to convert and import a fairly convoluted Filemaker Pro 5
> database to MySQL. I have exported the filemaker records as tab
delimited
> files and have managed to load them in to MySQL without too much
problem.
> However I have one issue: Dates. Of course MySQL stores dates in
YYYY-MM-
> DD
> whilst filemaker uses DD-MM-YYYY and so when I import my delimited
text
> file
> MySQL interprets the dates incorrectly. I can't seem to find a way of
> changing the date format in Filemaker, nor can I figure out how to get
> MySQL
> to convert the dates into it's format. Any help would be greatly
> appreciated and I apologise if this is something simple but this is my
> first
> entanglement with databases.
>
> I am using MySQL 3.23.49 running on FreeBSD 4-STABLE. MySQL was
compiled
> from the FreeBSD port.
>
> Thanks for your help,
>
> Cameron
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <mysql-unsubscribe-
> [EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
