?
Solved

MySQL Sort by Date issue

Posted on 2014-09-02
5
Medium Priority
?
427 Views
Last Modified: 2014-09-03
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
Comment
Question by:BrianFord
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 79

Accepted Solution

by:
arnold earned 2000 total points
ID: 40299994
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
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 40300001
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
 
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 40300021
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40300417
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
 

Author Closing Comment

by:BrianFord
ID: 40300679
Thank you, that worked just fine :)
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question