CODEWITHSUNDEEP
phpmysql
Neeraj Sharma
Neeraj Sharma

Reputation: 71

How to sort date-time stored as varchar data type in AM/ PM ascending order?

I have stored data in my table. I want to sort my column named new_meeting_time as varchar. I have a query SELECT * FROM table_name ORDER BY new_meeting_time ASC and gives me the following output:

      1. 17-03-2016 01:26 PM
      2. 17-03-2016 01:31 PM
      3. 17-03-2016 01:34 PM
      4. 17-03-2016 01:44 PM
      5. 17-03-2016 10:33 AM

But I want to get data in the following order: 

        1. 17-03-2016 10:33 AM  
        2. 17-03-2016 01:26 PM
        3. 17-03-2016 01:31 PM  
        4. 17-03-2016 01:34 PM
        5. 17-03-2016 01:44 PM

How can i do this?

Upvotes: 2

Views: 607

Answers (3)

Your Common Sense
Your Common Sense

Reputation: 157917

Store your data in a proper format, which is datetime.

Solutions from the other answers are just awful performance-wise.

A database is a fine mechsnism. And one should use it properly. If you ask how to harness a horse to drive a car, the proper answer is "buy gasoline".

Upvotes: 5

Dylan Su
Dylan Su

Reputation: 6065

Try this utilizing STR_TO_DATE.

SELECT * FROM table_name ORDER BY STR_TO_DATE(new_meeting_time,'%d-%m-%Y %h:%i %p');

Demo for the effect of this function:

mysql> select STR_TO_DATE('17-03-2016 01:26 PM','%d-%m-%Y %h:%i %p');
+--------------------------------------------------------+
| STR_TO_DATE('17-03-2016 01:26 PM','%d-%m-%Y %h:%i %p') |
+--------------------------------------------------------+
| 2016-03-17 13:26:00                                    |
+--------------------------------------------------------+
1 row in set (0.00 sec)

Upvotes: 0

J.K
J.K

Reputation: 1374

SELECT *
FROM table_name
ORDER BY STR_TO_DATE(new_meeting_time, '%d-%m-%Y %h:%i:%s') ASC

Upvotes: 0

Related Questions