CODEWITHSUNDEEP
mysqlsql-order-by
user9431147
user9431147

Reputation:

Order date/time VARCHAR by DESC in MySQL

I have these 3 VARCHAR data:

Mar 06 2018 06:42:08
Mar 04 2017 23:21:15
Feb 21 2018 14:48:46
Jul 29 2016 11:56:22
Mar 06 2018 06:41:54

I want to know how I can structure my query:

SELECT `users`.`last_online` FROM `users` ORDER BY ... DESC

So that I can get the following output

Mar 06 2018 06:42:08
Mar 06 2018 06:41:54
Feb 21 2018 14:48:46
Jul 29 2017 11:56:22
Mar 04 2017 23:21:15

NOTE I don't want to use DATETIME, I am using VARCHAR, please don't suggest otherwise.

UPDATE

I tried the following query, based on the documentation from Uuerdo's answer, but although it seemed it would work, it didn't:

 SELECT `users`.`last_online` FROM `users` ORDER BY STR_TO_DATE(`users`.`last_online`, '%M %d %Y %h:%i:%s') DESC

I need it to consider MM/DD/YYYY H:i:S. Which functions would I need to use to structure this query properly, and what would be the resultant query?

Upvotes: 0

Views: 238

Answers (1)

clinomaniac
clinomaniac

Reputation: 2218

Try using this:

SELECT `users`.`last_online`,
FROM `users` 
ORDER BY STR_TO_DATE(`users`.`last_online`, '%M %d %Y %H:%i:%s') DESC

You need to use '%H' instead of '%h' for 24 hour time.

Upvotes: 1

Related Questions