Solved

MySQL Sort by Date issue

Posted on 2014-09-02
5
415 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 109

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikipedia.org/wiki/PHP  Very powerful.  But a…
Creating and Managing Databases with phpMyAdmin in cPanel.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

785 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