glennn.php Posted July 2, 2008 Share Posted July 2, 2008 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 Link to comment https://forums.phpfreaks.com/topic/112971-problem-with-date-field-format-ordering-data/ Share on other sites More sharing options...
PFMaBiSmAd Posted July 2, 2008 Share Posted July 2, 2008 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. Link to comment https://forums.phpfreaks.com/topic/112971-problem-with-date-field-format-ordering-data/#findComment-580285 Share on other sites More sharing options...
bluejay002 Posted July 3, 2008 Share Posted July 3, 2008 @PFMaBiSmAd I agree. Just change the field to date. it should fix it. Link to comment https://forums.phpfreaks.com/topic/112971-problem-with-date-field-format-ordering-data/#findComment-580672 Share on other sites More sharing options...
glennn.php Posted July 3, 2008 Author Share Posted July 3, 2008 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. Link to comment https://forums.phpfreaks.com/topic/112971-problem-with-date-field-format-ordering-data/#findComment-580775 Share on other sites More sharing options...
fenway Posted July 3, 2008 Share Posted July 3, 2008 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. Link to comment https://forums.phpfreaks.com/topic/112971-problem-with-date-field-format-ordering-data/#findComment-580777 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.