Unable to convert a list field to a date/time field

Hi,

The problem is that the data that is currently stored in the field must be converted to MySQL date format before you can convert the field type. Usually I use a script to convert the data first, something like this should work:

  //Get All records from section with date field
  $blogs = mysql_select('blog');

  
  //Loop through records
  foreach($blogs as $blog){
    
    /*
    * Assuming current field is just the year in format 07 or 2007
    * Create basic date
    */
    $dateString = $blog['year'].'-01-01 00:00:00';
    $dateString = date('Y-m-d H:i:s', strtotime($dateString));

    mysql_update('blog', $blog['num'], null, array('year' => $dateString));
    echo $blog['num']." converted!<br>";
  }

  echo 'DONE!';

This is just example code, so you'll have to make a few changes to get it working with your site. Make sure that you backup your database before using this code!

So the mysql_select function will retrieve all of the records from the table with your date field that needs converting. Then the foreach loop passes through each record and converts the year field into a date the mysql date format. In this example code I've assumed the date is stored in the format 12 or 2012. Finally the mysql_update function is used to save the information back into the record.

Let me know if you have any questions.

Cheers

Greg

Hi Greg,

I'm not versed in direct MySQL manipulation so I'll ask "the" question..."Where" do I run this script exactly?  Do I create a .php page, upload the page and then run it via a browser? Is this something I'm to do through phpMyadmin (which scares me because I don't know what I'm doing in there)? Thanks!

Hi,

You'd need to create a .php page and upload it to your server, then run it in your web browser. You only need to run it once, then you can delete it. 

Cheers

Greg

Excellent - thanks Greg! As always...you guys rock!!!!