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 :)
BrianFordAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
Your date field seems to be a varchar rather than a date/time after the conversion.
To avoid this, when using the order by do not convert, use the order by incomedate in the final query.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dave BaldwinFixer of ProblemsCommented:
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.
0
Cornelia YoderArtistCommented:
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)
0
Ray PaseurCommented:
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.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
0
BrianFordAuthor Commented:
Thank you, that worked just fine :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.