0

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?

  • 3
    https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_str-to-date – Uueerdo Mar 08 '18 at 21:10
  • Since your date has month name, the only way to order it without converting it to date is to hardcode all the months in order. You'll have to say something like Jan = 1, Feb = 2....and so on. It would be a lot easier to convert to date and sort it. Don't need to save the converted values, just use it for sorting. – clinomaniac Mar 08 '18 at 21:17
  • @Uueerdo Thanks for the very broad answer by linking me to something that I already read through which lead me to asking this question, such a good helper you are. –  Mar 08 '18 at 21:19
  • @clinomaniac How would I do that, and which function am I looking to use? Implementing functions in my SQL queries is new to me. I've worked all my life with C++ and WinAPI, the only work I've done with SQL is the C++->MYSQL connector library. But nothing directly. –  Mar 08 '18 at 21:19
  • 1
    @SymcoJohnson Don't need to be sarcastic and dismissive to someone trying to help you. You haven't mentioned that you looked at the site and Uueerdo thought it might help. Are you fine with converting the varchar to date for ordering or are you trying to order the string by hardcoding the values? – clinomaniac Mar 08 '18 at 21:23
  • @clinomaniac As long as I don't have to touch the design of my current database, and I only have to convert the string to date inside the query. –  Mar 08 '18 at 21:24
  • Possible duplicate of [MySQL ORDER BY Date field which is not in date format](https://stackoverflow.com/questions/16936181/mysql-order-by-date-field-which-is-not-in-date-format) – Paul Spiegel Mar 08 '18 at 21:25
  • @PaulSpiegel Check my update, I actually just tried that method, and it didn't work. –  Mar 08 '18 at 21:26
  • "didn't work" is not a problem statement. – Paul Spiegel Mar 08 '18 at 21:27
  • @SymcoJohnson you'd be surprised by how many people are unaware of the level of documentation MySQL provides, or how many people can't be bothered to look. – Uueerdo Mar 08 '18 at 22:03

1 Answers1

1

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.

clinomaniac
  • 2,200
  • 2
  • 17
  • 22
  • Changing the %H from lowercase to uppercase caused the query to work properly. Thanks! –  Mar 08 '18 at 21:30