problem with DATE field format, ordering data

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
glennn.php
Forum Commoner
Posts: 41
Joined: Sat Jul 08, 2006 12:26 pm

problem with DATE field format, ordering data

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: problem with DATE field format, ordering data

Post 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.
Post Reply