Proper Date sorting

cmdr_bond

Hello,

I have a database with one field in it that lists the date of an event (ie: May 2nd, 2005 7:00PM). I'm trying to output that information in order of how it occurs. That works find for dates within the same month, but once it gets to June dates, June appears before May. Is there an easy way to make that all appear in correct order in the database, or is my best alternative to split the date into two fields one the month and the other is the rest of the date. So I can sort the month through IF() statements and just sort the rest alphabetically ascending.

Thanks

thorpe

what type of field is this 'date' in? is it a 'datetime' field?

i think your date should look more like this;

05/02/2005 07:00:00PM

and should be in a 'datetime' type field, then you can just use an 'ORDER BY' clause in your query.

of course im sure theres other ways to store a date, but thats how i do it.

cmdr_bond

And then is it easy to convert that format into: Month day(st,nd,rd,th), year time?
I know if I don't include the time, I could easily do:

$date = "04/30/1973";
list($month, $day, $year) = split('[/.-]', $date);
echo "Month: $month; Day: $day; Year: $year<br />\n";

but how can I divide the $year part too? (I guess for the $day, I could put an IF() clause to add those suffixes.)

Is this the easiest way to do it, or does PHP convert the date automatically?

Thanks

tekky

you can store them as an INT in unixtimestamp format... then use PHP's [man]date[/man] function to get Y D M and the suffixes.... and the ORDER BY will work with those as well as a smaller # is an older day and a larger # is a more current day or farther into the future....

as per getting just the date from a DATETIME column....

$sql = 'SELECT DATE(datetime_column) as just_the_date FROM sometable';

that will gate you just_the_date = YYYY-MM-DD (SQL format)
or...

$sql = 'SELECT DATE_FORMAT(datetime_column,"%b") AS curmonth, DATE_FORMAT(datetime_column,"%D") AS curday, DATE_FORMAT(datetime_column,"%Y") AS curyear, DATE_FORMAT(datetime_column,"%a") AS curdow FROM someTable';

that will get you curmonth = Mar, curday = 15th, curyear = 2005, curdow = Wed