RE: [PHP] convert VARCHAR 10 to DATETIME

thanks. this was the quickest way to accomplish the task strtotime(); also works. both are useful to know... ----- Original Message ----- From: "John W. Holmes" <holmes072000@charter.net> Date: Wed, 19 Mar 2003 21:47:23 -0500 To: "'freaky deaky'" <freaky_deaky@linuxmail.org>, <php-general@lists.php.net> Subject: 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/ > > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > -- ______________________________________________ http://www.linuxmail.org/ Now with e-mail forwarding for only US$5.95/yr Powered by Outblaze