RE: [PHP] convert VARCHAR 10 to DATETIME

> 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 If you want to do it strictly in SQL, then use MySQL's String functions to pull apart the M/DD/YYYY date and format it into a valid MySQL Date. If you can run the whole thing through PHP, you may be able to use strtotime(). For the SQL solution, create a new column in your table that is a Date type. We'll call it f_date for this example. The following query will break apart the M/DD/YYYY and format it as YYYY/MM/DD, which is a format MySQL will accept. UPDATE table SET f_date = CONCAT(RIGHT(old_date,4), '/', SUBSTRING_INDEX(old_date,'/',2)); Hope that helps. ---John W. Holmes... PHP Architect - A monthly magazine for PHP Professionals. Get your copy today. http://www.phparch.com/