[SOLVED] changing a field contents and putting it back in the field

[SusieWoosey]

Server version: 5.0.26-community-nt, MySQL client version: 5.0.22, phpMyAdmin - 2.9.1

 

I have a field in my table which holds a date. I started out by filling in the date in standard UK date format DD-MM-YYYY. Or at least, what I would call standard UK date. However, I find now that it would have been better to use the format YYYY-MM-DD.

 

My problem, how can I alter this field so the date reads as YYYY-MM-DD?

 

I have tried in SQL from with phpMyAdmin, but all I get is and error with the code I tried, so I tried to do it from a small php program. Firstly with a copy of the table, but all I succeeded in doing was filling the field with same piece of data -  the first field it checked. So I changed things and now it fills the field with the last piece of data it finds. I created a second column and tried to get the prgram to feed the corrected date to that, it sort of does, but it stills ends up filled with the same piece of data.

 

How to I get my program to increment and putin the correct piece of data?

 

 

A snippet of the code I am using is as follows:- The echo's show me that I am changing the date around.

 

if ($result !=0)

{

while ($myrow = mysql_fetch_array($result))

{

 

$mydate = $myrow['mydate'];

 

echo $mydate . "  ";

$temp_date = date("Y-m-d",strtotime($mydate));

 

echo $temp_date . "  ";

 

 

$searchstring = "UPDATE tracks2 SET newdate = '$temp_date'";

 

$fred = mysql_query($searchstring);

 

echo $fred . "  ";

$fred++;

 

echo $fred . "  ";

 

}

}

 

 

Any help and suggestions would be appreciated.

 

 

Suzanne.

[PFMaBiSmAd]

Your field needs to end up being a DATE data type. It is probably a VARCHAR now. Since a field can only be one type at any time, you need to add a new column with a DATE data type, then convert and place the values into the new column.

 

You can use the mysql STR_TO_DATE() function directly in a query to convert the values. Since this is a one time operation, it would be easier to do this using phpMyAdmin.

[SusieWoosey]

Yes it is VarChar, I will try that.

 

Thanks for the suggestion.

 

 

Suzanne

[SusieWoosey]

Sorted.

 

 

UPDATE tracks2 SET newdate = (SELECT STR_TO_DATE(mydate, '%d-%m-%Y'))

 

 

solved the problem, I now have my dates sorted, and it is now a DATE field too.

 

 

Thanks for your help PFMaBiSmAd.

 

 

Suzanne is a happy bunny again.