Sort by varchar date entry in SQL-CodePudding

I'm using mysqli and PHP.

I want to sort my table by DATE which is a varchar holding date information like this:

January 06, 2023 // format is Month Day, Year

I want to sort by date but it doesn't work since this is not datetime. I've tried casting DATE but it doesn't seem to work. I tried like this:

$sql = 'SELECT * FROM receipt WHERE ID=? ORDER BY CAST(DATE AS datetime) DESC';

I also tried convert but it did not work either, I must be using the syntax wrong.

How can I sort by date if my DATE entry is of type varchar.

CodePudding user response：

In MySQL you can use str_to_date with the appropriate date format to convert a varchar to a date:

SELECT * FROM receipt WHERE ID=? ORDER BY STR_TO_DATE(date, '%M %d, %Y') DESC