I have a database in which the date is stored in varchar field in a following format: date("F, d, Y"); ex: May, 27, 2008, now the problem is that I want to change that field into mySQL date field as well as convert my older dates into MySQL date format i-e Y-m-d (2007-08-06) Thanks in Advance Raja
Let me get this straight. You want to be able to edit a field in a database while still keeping all current entries? If this is right, the way i would do it is to create a script to pull all the records from the database use PHP strtotime the convert the date strings into formatted dates. Edit the database field using alter and then insert them back into the database. Dont know if this is what you were looking for but i hope it helps a little. All the best.
Alter table mytable addcolumn tempcolumn date update mytable set tempcolumn=convertfromoldtonew(mydatecolumn) altertable drop column mydatecolumn alter table rename column mynewdatecolumn to mydatecolumn In short create the correct date column with a temp name update this tempcolumn with converted values from your old column, drop the oldcolumn and rename your temp to your actual columnname. Just google or search the forums for the correct syntax, advantages -The database server does the datamanagement / conversion for you (That's its task) - Much much shorter runtime then using an external language to do this for you - Renaming columns is ussualy very fast, so actual production downtime is minimal. - No dependancy on using / learning PHP or any other language