problem with DATE field format, ordering data

[glennn.php]

 

I originally created a db with a particular field (ck_pt) that holds a date as VARCHAR(15) - now they want the output sortable by that field DESC...

 

the dates that are in there are like such 7/25/08 - when i switch the TYPE to DATE, the vaalues go to 07-25-2008, or perhaps 25-07-2008 - i dunno, but SOME of the values go to 00-00-0000 (GOD i'm glad i backed it up).

 

why am i losing some of the values, and what can i do about getting these fields converted properly and queried into a page where %d/%m/%y will be correctly ordered, 2008 before 2009, of course...?

 

i really appreciate your help.

 

GN

[PFMaBiSmAd]

Just changing the column type does not change the data in it. You will need to add a new column of the correct type and copy/format the existing values into the new column. Once you have all your code working with the new column, you can delete the old column. You an use the mysql STR_TO_DATE() function in a single UPDATE query (with no WHERE clause) to copy and format the existing values into the new column.

[bluejay002]

@PFMaBiSmAd

I agree.

 

Just change the field to date. it should fix it.

[glennn.php]

Just changing the column type does not change the data in it.

 

hate to disagree, but this is in fact exactly what happened. some of the dates were changed to 00-00-0000. promise. wouldn't lie to you.

[fenway]

Just changing the column type does not change the data in it.

 

hate to disagree, but this is in fact exactly what happened. some of the dates were changed to 00-00-0000. promise. wouldn't lie to you.

I think PFMaBiSmAd meant that it won't STR_TO_DATE() it for you.