VARCHAR to DATETIME

philosophia

this question is not strictly php related, but i thought possibly someone here might have dealt with this type of problem before.

i am exporting a database out of filemakerpro 5 as a comma delimited file, and
importing the data into a mysql database

most of the information transfer over ok, the one big problem i have is
filemaker's date/time format.

in the filemaker pro database, it looks like the timestamp is stored in the
format: M/DD/YYYY

mysql will not let me create a DATETIME field with X/XX/XXXX as the default
value. it automatically changes the default value to '0000-00-00'.

i will migrate the filemaker pro data, using VARCHAR 10 to store the timestamp
information, but there needs to be a way to go through every entry in this
column, convert a string such as
'M/DD/YYYY' to something compatible with mysql's DATETIME format.

the reason i'd like to convert the filemaker-generated 'M/DD/YYYY' timestamp
string, which would be stored as
VARCHAR in the db - to something like '0000-00-00' - is that i'd like to convert
the the data type of this field
from varchar 10 to DATETIME with a default value of 0000-00-00, so that any sql
queries searching through the
database by date will be compatible, as will any future entries that get tacked
onto this field in the database
after the migration from filemaker, which will be in DATETIME format

i don't know if this can be accomplished by php, or some other type of scripting
language. i'd like to avoid
having to manually edit the timestamp information if i can avoid it...

if anyone has any suggestions, i'd really appreciate it

thank you

daynah

You can write a script that will convert the times. This is a simple way of manipulating the dates (strings). A better way of doing it is by using regular expressions of course, but I'm not too great with that.. heh

<?

/***********************************
 You want: 03/19/2003 -> 2003-19-03
***********************************/

$the_date = "03/19/2003";
$new_date = explode('/', $the_date);
$the_new_date = $new_date[2] . '-' . $new_date[1] . '-' . $new_date[0] . ' 00:00:00';

echo 'The old date was: ' . $the_date . '<br />';
echo 'The new date is: ' . $the_new_date;

?>

philosophia

can you then insert the resulting string
$the_new_date
into a field of data type DATETIME in a mysql db? what i'm confused about is - we'd be using explode() to reformat an existing string to make a new string, but i'm not sure if you can just take the new string and just stick it in a DATETIME field, and be able to run timestamp-related sql queries and have mysql treat it the same way as a legitimate auto generated timestamp

is this possible?

daynah

You'll have to write a small converter that takes the first date, manipulate it, and then send it back to the new database.

So the first thing you would have to do is SELECT from the old database. Get the id, and the old date format. Second, make the date into a format you would like it to be (using explode or regular expression). Third, after getting the new date, UPDATE the new database WHERE id = the one you selected SET the_date_field = $the_new_date.