Page 1 of 1

problem with DATE field format, ordering data

Posted: Wed Jul 02, 2008 2:18 pm
by 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 records 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

Re: problem with DATE field format, ordering data

Posted: Wed Jul 02, 2008 6:03 pm
by califdon
glennn.php wrote: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 records 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
Good for you. Backing up data before doing dangerous things like changing field definitions is crucial.

Of course you now realize that dates belong in a Date type field. The reason some of them are not converting is no doubt because some of the records have text data that isn't a valid date, so it defaults to 00-00-0000. It's pretty hard to do much about that, programatically, because how does the system know what to do with something that doesn't translate into a date, or possibly represents a date either earlier or later than the date domain that a particular Date type supports? Your best bet is to Alter your table to add a new column with the Date type you want to use. Refer: http://dev.mysql.com/doc/refman/5.0/en/datetime.html. Then you can try queries that populate the new field without destroying the old data. If there are some invalid dates in the text fields, you can sort for them and if there aren't TOO many of them, you can individually decide what to put into them.