Solved

MySQL Sort by Date issue

Posted on 2014-09-02
5
413 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
5 Comments
 
LVL 77

Accepted Solution

by:
arnold earned 500 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 83

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:yodercm
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 108

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Creating and Managing Databases with phpMyAdmin in cPanel.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

863 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now