CODEWITHSUNDEEP
mysql
MR.G
MR.G

Reputation: 37

How to Sort Varchar date in Descending order MySql?

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.

Upvotes: 0

Views: 93

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

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;

Upvotes: 1

Related Questions