Ordering by DATE from two DATE columns

[nightmicu]

Hi everyone,

I have been trying to figure this one out for hours and I have about had it. I am trying to pull event details from a table (events) and arrange them by their respective date. Here comes the tricky part - some events have a specific date (DATE field, YYYY/MM/DD - column event_date) and others have only Month and Year - also DATE, stored as user submitted YYYY/MM/01 (column event_date_gen) ... the 01 since they are not entering a specific day.

Here is the SQL I am using... although I have tried MANY variations:

SELECT event_id, event_title, DATE_FORMAT(event_date, '%W, %b %D, %Y') AS event_date, DATE_FORMAT(event_date_gen, '%M, %Y') AS event_date_gen, event_description FROM events ORDER BY event_date DESC, event_date_gen DESC"

Then I am using a WHILE loop to return the results:


<?php
while ($eventDetails = mysql_fetch_assoc($getEvents)) {
echo "<p>$eventDetails[event_title] - $eventDetails[event_date]$eventDetails[event_date_gen]</p>";
}
?>


The results I am getting, with the above SQL, is as follows:


Annual Meeting and Reception - Sunday, Sep 13th, 2009

Spring Lecture with Cori Ellison - Sunday, Feb 28th, 2010

Trip to New York City Opera - Partenope - Sunday, Apr 11th, 2010

Pot Luck Supper and Opera Video - October, 2009

Wine Tasting Benefit - October, 2009

As you can see, the event_date_gen ends up at the bottom instead of after the event on Sep 13th. Different variations of the SQL have returned similar out of order results.

PLEASE help. This is holding me back on this project.

Many thanks,

Ben

[forum_amnesiac]

Have you given this syntax a try


SELECT event_id, event_title, DATE_FORMAT(event_date, '%W, %b %D, %Y') AS event_date, DATE_FORMAT(event_date_gen, '%M, %Y') AS event_date_gen, event_description FROM events ORDER BY (event_date / event_date_gen) DESC"

[nightmicu]

Found the solution to this one while I was kayaking... guess logic comes when you get away from the keyboard.

I changed the column event_date_gen to a VARCHAR(3) and set it to "yes" if only the month and year is set (day is always 01 for these entries), "no" if a full date is specified. Dates, regardless of whether or not the day was entered, are stored in event_date. Then I used PHP in pages that pull from this table to format the date (Month, DD, YYYY for full date, Month, YYYY for month only) based on the value of "event_date_gen."

End result is a listing of events, in order, with the properly formatted date for each. I'm sure that there is another way I could have accomplished this but hey, it works

Ben