CODEWITHSUNDEEP
mysqlsql
vaindil
vaindil

Reputation: 7864

Data not sorted properly using str_to_date

I have a bunch of dates in a table in the format August 31, 2013, 5:02 am. I need to convert these to dates and sort on the dates. I'm using the below query, but it returns the dates in a strange way.

SELECT name, date, link FROM table_name ORDER BY str_to_date(date, '%M %e, %Y, %l %p')

So, for an example set of data, I have:

August 31, 2013, 5:02 am
October 10, 2013, 2:14 pm
October 14, 2013, 7:00 pm
October 22, 2013, 1:46 pm
October 22, 2013, 2:47 pm

When the query runs, however, the sorting occurs like this:

August 31, 2013, 5:02 am
October 22, 2013, 2:47 pm
October 22, 2013, 1:46 pm
October 10, 2013, 2:14 pm
October 14, 2013, 7:00 pm

What do I need to do to correct the query and display the data properly?

Upvotes: 0

Views: 67

Answers (1)

Thomas Kelley
Thomas Kelley

Reputation: 10302

It will work if you use %l:%i for the time part, instead of just %l:

mysql> SELECT str_to_date(date, '%M %e, %Y, %l:%i %p') FROM table_name ORDER BY str_to_date(date, '%M %e, %Y, %l:%i %p');
+-----------------------------------------------+
| str_to_date(timestamp, '%M %e, %Y, %l:%i %p') |
+-----------------------------------------------+
| 2013-08-31 05:02:00                           |
| 2013-10-10 14:14:00                           |
| 2013-10-14 19:00:00                           |
| 2013-10-22 13:46:00                           |
| 2013-10-22 14:47:00                           |
+-----------------------------------------------+
5 rows in set (0.00 sec)

Upvotes: 2

Related Questions