Manipulating date format MySQL
WordPress, mySQL
Importing an “alien” date column into a WordPress format - necessary because the old date column contained data in the “VARCHAR” format. WordPress requires date to be in the “DATE” format.
Delete unnecessary columns.
Rename columns to suit WordPress schema.
Rename the old date column to “OLDDATE”.
Create a new column with the data-type “DATE”, named “post_date”:
- Open phpMyAdmin
- Select “Structure”
- Add a new Column
- Name appropriately (e.g. WP date = “post_date”)
- Select Type “DATE”
Run the following SQL command:
UPDATE tablename SET post_date = str_to_date( OLDDATE, '%d/%m/%Y')
Where old dates are in string data-format, dd/mm/yyyy.
Manipulate Column Data
Copy data from ColumnA Data to ColumnB:
UPDATE tablename SET columnB = columnA
Delete table data:
TRUNCATE tablename
Import Table Data, phpMyAdmin
Organise data on a test table, then rename before exporting:
- Select table
- Select operations
- Select “rename table to”
- Give the table the same name as the target table
Click “Export” in top level menu.
Open the target DB, select the target table. Click import and select the recently exported DB.
comments powered by Disqus