• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 432
  • Last Modified:

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 :)
0
BrianFord
Asked:
BrianFord
1 Solution
 
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
 
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now