How to order DATETIME rows with [] in MySQL

Question

I have a mysql "users" table like this example:

id  | user    | created
100 | user001 | [27-01-2016 04:30 PM]
101 | user005 | [19-05-2017 09:28 AM]
102 | user019 | [09-10-2015 03:29 PM]
103 | user029 | [18-11-2017 05:40 PM]

And I want to get this table in descending order by created row like this:

id  | user    | created
103 | user029 | [18-11-2017 05:40 PM]
101 | user005 | [19-05-2017 09:28 AM]
100 | user001 | [27-01-2016 04:30 PM]
102 | user019 | [09-10-2015 03:29 PM]

I tried with DATE_FORMAT:

SELECT id,user,DATE_FORMAT(created,"%d %m %Y") AS created FROM users ORDER by created desc

But it shows nothing, how can I get it?

I'd like some help.

You need [STR_TO_DATE](https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date) instead of `DATE_FORMAT`. But it would be better to use DATETIME or TIMESTAMP for your column instead of string. — Paul Spiegel, Dec 28 '17 at 21:21
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, Dec 28 '17 at 21:23
@NekoLopez Are your columns `DATETIME` as it says in the question? If they are not then I will delete my asnwer and check the link above. — Ivan86, Dec 28 '17 at 21:25
@PaulSpiegel Actually, he needs both. `STR_TO_DATE()` to parse the date and order it, `DATE_FORMAT()` to display the output in a different format. — Barmar, Dec 28 '17 at 22:20
@Barmar I don't see anything about "display the output in a different format" in the question. The expected result has the same format as original data. — Paul Spiegel, Dec 28 '17 at 22:26
@PaulSpiegel Good point, I wonder why he used `DATE_FORMAT` in the `SELECT` list. — Barmar, Dec 28 '17 at 22:27

Ivan86 · Answer 1 · 2017-12-31T18:09:37.287

This works:

SELECT `id`, `user`, DATE_FORMAT(STR_TO_DATE(MID(`created`, 2, 19), '%d-%m-%Y %h:%i %p'), '%d-%m-%Y %h:%i %p') 
AS `created:`  FROM `users` ORDER BY STR_TO_DATE(`created:`,'%d-%m-%Y %h:%i %p') DESC

Here is a working SQL Fiddle.

Explanation:

The MID() MySQL function returns the middle part of a string, so we easily stripped out the []. After that, the STR_TO_DATE() function parses the returned string into DATETIME format (the %p is for AM/PM). The returning value is not properly formatted, at least not in the way you would wish to have it. So we then use DATE_FORMAT() to format the output. In the end, we use STR_TO_DATE() once again for the ordering since created: is returned as string.

I also changed your created output column to created: since it will not work if they are all the same name.

score 0 · Answer 2 · answered Dec 29 '17 at 10:47

Your created date format isn't correct, you should alter the table and change the field from varchar/char(string) to timestamp type. I have already tried converting your string dates with 'STR_TO_DATE()' but it returns null because the format doesn't match either timestamp, date or time formats. Its always best practice to store dates/datetime as either date or timestamp data types. You can always apply formatting functions later in query to output dates to any desired format.

How to order DATETIME rows with [] in MySQL

2 Answers2