Menu
I am currently experiencing a problem with sorting a result by a data column which is using date_format.
I have the below dates:
When I perform the query:
SELECT date FROM myTable ORDER date DESC
The dates are ordered in the correct order
When I perform the query
SELECT DATE_FORMAT(date, '%d-%m-%Y') as `date` ORDER BY date
The dates are now in the wrong order
I've also tried running the query
SELECT DATE_FORMAT(date, '%d-%m-%Y') as date ORDER BY DATE_FORMAT(date, '%d-%m-%Y') but has made no difference.
How can I get this to sort in the correct order.
304
Use the ORDER BY keyword and the name of the column by which you want to sort. This way, you'll sort the data in ascending order by this column. You could also use the ASC keyword to make it clear that the order is ascending (the earliest date is shown first, the latest date is shown last, etc.).
You can use the following query. SELECT column1,column2, UNIX_TIMESTAMP(CONCAT( event_date ,' ', event_time )) as unixtimestamp from table_name Order by unixtimestamp desc; Note:(There is a space between event_date and event_time in the CONCAT function.
The ORDER BY statement in SQL is used to sort the fetched data in either ascending or descending according to one or more columns. By default ORDER BY sorts the data in ascending order. We can use the keyword DESC to sort the data in descending order and the keyword ASC to sort in ascending order.
To order by month, create a date with this month. To do this, use the STR_TO_DATE() function. If you have a date stored as a string in the ' Year Month Day ' format, you can cast it to a date using STR_TO_DATE(date_string, '%Y %M %d') . The CONCAT() function combines all the arguments into one string.
The problem is that you are overriding the column name with the alias.
Choose another alias:
SELECT DATE_FORMAT(`date`, '%d-%m-%Y') as date_formatted
FROM myTable
ORDER BY `date` DESC
Just specify table name for the column in ORDER BY clause:
SELECT DATE_FORMAT(date, '%d-%m-%Y') as `date`
FROM myTable
ORDER BY myTable.`date` DESC -- <<<<<<
In this case mysql knows you want to sort by table column, not by the expression you've evaluated in the SELECT part
6
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
