-1

I want to sort data by varchar date in ascending order and time in descending order.

Here is my Table: -

 name      date     time
 hulk  30-Nov-2021 02:20 PM
 hulk  01-Dec-2021 02:20 PM
 hulk  02-Dec-2021 02:20 PM
 hulk  01-Dec-2021 02:20 PM
 hulk  30-Nov-2021 02:20 PM
 hulk  30-Nov-2021 02:10 PM
 hulk  03-Dec-2021 01:34 PM

Here is my query:

SELECT * 
FROM `News_Data` 
ORDER BY Date ASC , STR_TO_DATE(Time, '%l:%i %p') DESC;

after runing a query we get the result like this:

 name      date     time
 hulk  01-Dec-2021 02:20 PM
 hulk  01-Dec-2021 02:20 PM
 hulk  30-Nov-2021 02:20 PM
 hulk  01-Dec-2021 02:20 PM
 hulk  02-Dec-2021 02:20 PM
 hulk  30-Nov-2021 02:10 PM
 hulk  03-Dec-2021 01:34 PM

but i want like this:

 name      date     time
 hulk  03-Dec-2021 01:34 PM
 hulk  02-Dec-2021 02:20 PM
 hulk  30-Nov-2021 02:20 PM
 hulk  01-Dec-2021 03:20 PM
 hulk  01-Dec-2021 02:23 PM
 hulk  01-Dec-2021 01:15 PM
 hulk  30-Nov-2021 02:10 PM

someone can help me to tackle this problem.

ZIBRAN KHAN
  • 89
  • 4
  • you have to convert the VARCHAR to a DATE, see: [How to convert a string to date in MySQL?](https://stackoverflow.com/questions/5201383/how-to-convert-a-string-to-date-in-mysql) BTW: The ordering in the "but I want like this": Is this REALLY the wanted order ? – Luuk Dec 03 '21 at 10:20
  • we already try this code but it doesn't work. – ZIBRAN KHAN Dec 03 '21 at 10:29
  • Please stop using "it doesn't work". Show what you really tried, and tell why the output is different from what you want to get. – Luuk Dec 03 '21 at 10:37
  • I got the answer : https://stackoverflow.com/a/70212781/13552550 – ZIBRAN KHAN Dec 03 '21 at 11:03
  • The real issue is storing dates and times as strings in your table. Formatting is a presentation decision not a data type decision. Constantly converting string values to dates and times so that you can sort or search may have very little cost with a tiny test dataset but doing this in large production datasets can be very costly. Avoid getting into a really bad habit. – user1191247 Dec 03 '21 at 19:29

1 Answers1

0

You nee str_to_date for date column too

SELECT * 
FROM `News_Data` 
ORDER BY STR_TO_DATE(Date,'%d-%b-%Y')  ASC, STR_TO_DATE(Time, '%l:%i %p') DESC;
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107