Date_Format

[kevinfwb]

I have a query that is using the Date_Format feature to change it from YYYY-MM-DD to MM-DD-YYYY.  The table head is a link that will sort it based on which table header you click on.  When I click on the table header for any item that is using Date_Format, it sorts it based on the month instead of the year?

 

For example it will return

01-01-2010

01-01-2014

01-02-2020 

01-03-2008

 

Instead of

01-03-2008

01-01-2010

01-01-2014

01-02-2020

 

Is there any way to make it sort based on the actual chronological order instead of sorthing chronologicaly by month? 

Code Snippet

**********************************************************************************

 

$query = "SELECT unitNum, LeaseEnd, RenewalDaysNotice1, RenewalNumOptions, RenewalOptionsRemain, DATE_FORMAT(NoticeDate, '%m-%d-%Y')

AS NoticeDate, DATE_FORMAT(ActionDate, '%m-%d-%Y') AS ActionDate  FROM unit

ORDER BY $sort ASC";

 

 

echo "<table name=\"MainTable\" class=\"MainTableGrid\">\n";

echo "<tr>\n";           

echo "<th><a href=\"$PHP_SELF?sort=unitNum&count=[$count+1]\">Unit</th>\n";

echo "<th><a href=\"$PHP_SELF?sort=LeaseEnd&count=[$count+1]\">Current Expiration</th>\n";

echo "<th><a href=\"$PHP_SELF?sort=RenewalDaysNotice1&count=[$count+1]\">Days Notice</th>\n";

echo "<th><a href=\"$PHP_SELF?sort=NoticeDate\">Notice Due</th>\n";

echo "<th>Action Date</th>\n";

echo "<th>No. of Options</th>\n";

echo "<th>Opts Remain</th>\n";     

 

echo "\n";           

echo "</tr>";

 

[bwochinski]

Maybe try renaming your formatted dates in the SELECT to something besides the same field names.

[kevinfwb]

After some researching, I figured out that Date_Format returns a string, which would explain why it's sorting they way that it is.

 

I previously had it where it would change it from YYYY-MM-DD to MM-DD-YYYY in the echo statement by using

echo date ('m-d-Y', strtotime ($row['LeaseEnd']));

 

It would sort chronologically, but then I ran into the 2038 year problem where any dates beyond 2038 will display as 2038. 

 

Does anyone have any ideas on how I can sort chronological with dates beyond 2038 in the MM-DD-YYYY format?

 

Thanks

 

-Kevin