How to Sort Varchar date in Descending order MySql?

Question

I'm storing datetime in varchar in my table. This my datetime format : 12/13/2019 12:05:30 PM. I want to sort these date in descending order. This is my sql

SELECT CreatedOn FROM advweb_usertemplatedesign Where UserId = 'fa69147ca33746cd8700a892298b11de' And ActiveStatus = 1 Order by CreatedOn Desc Limit 20

The result I'm getting is

12/5/2019 5:48:19 PM 12/4/2019 4:49:10 PM 12/3/2019 5:57:16 PM 12/13/2019 12:05:30 PM 12/12/2019 6:01:31 PM

Instead of

12/13/2019 12:05:30 PM 12/12/2019 6:01:31 PM 12/5/2019 5:48:19 PM 12/4/2019 4:49:10 PM 12/3/2019 5:57:16 PM

How to properly sort it? Thank you.

Does this answer your question? MySQL date format DD/MM/YYYY select query? — Devsi Odedra
– Devsi Odedra, Commented Dec 13, 2019 at 4:34
@DevsiOdedra no, it doesn't. Now getting 11/26/2019 3:21:36 PM, 11/27/2019 4:56:11 PM, 12/13/2019 12:05:30 PM, 11/27/2019 5:35:32 PM....it kinda mixed up now. — MR.G
– MR.G, Commented Dec 13, 2019 at 4:39

Tim Biegeleisen · Accepted Answer · 2019-12-13 04:40:04Z

You should avoid storing your dates as strings. As a temporary workaround, you may use MySQL's STR_TO_DATE function here:

SELECT CreatedOn FROM advweb_usertemplatedesign WHERE UserId = 'fa69147ca33746cd8700a892298b11de' AD ActiveStatus = 1 ORDER BY STR_TO_DATE(CreatedOn, '%m/%d/%Y %r') DESC LIMIT 20;

Collectives™ on Stack Overflow

How to Sort Varchar date in Descending order MySql?

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related