asked on

MySQL Sort by Date issue

I am fairly new to MySQL and have a table containing a 'DATE' column, there is no time portion.

The data in the column is in the format 'YYYY-MM-DD'

I am trying to select rows displaying the date as 'MM-DD-YYYY' and also order by this date field but the results are not ordering correctly, I have tried using the following statement:

select date_format(incomeDate, '%m-%d-%Y') from income order by date_format(incomeDate, '%m-%d-%Y');

Open in new window

but I'm getting results like:

01-02-2013
01-03-2012
01-03-2012
01-03-2013
01-03-2013
01-03-2013
01-03-2014
01-03-2014
01-04-2011
01-04-2012

Obviously these are not in date order

please help :)

ASKER CERTIFIED SOLUTION

arnold

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dave Baldwin

ORDER BY works on the result set. You are seeing the proper results for your request. It is common to do the date format conversion in your programming language since converting it in the result set changes the sorting.

Cornelia Yoder

As arnold says, order by the field, not the formatted date:

select date_format(incomeDate, '%m-%d-%Y') from income order by incomeDate;

As dave says, that will give you the order you want, for the formatted date.

(No points here, just giving the actual line of code you need)

Ray Paseur

If the column is defined as DATE, it will be an ISO-8601 standard date. ORDER BY works correctly with these columns.

SELECT DATE_FORMAT(incomeDate, '%m-%d-%Y') 
FROM income 
ORDER BY incomeDate;

Open in new window

In case you get into PHP, this article may be helpful.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

BrianFord

ASKER

Thank you, that worked just fine :)