Order by date and time

Question

I have two varchar fields which store the date and time in the following format: date=2010-08-18; time=07:53:55;

What would be the right sql query to show me the events from the most recent to the oldest

Answer 1

Aside from recommending that you use MySQL datetime fields rather than VARCHARs for any date or time values; what about something simple like adding an ORDER BY clause to your query:

ORDER BY `DATE` DESC, `TIME` DESC

Answer 2

Or you could convert them to datetime and then use the order by. To convert to date, use str_to_date like:

mysql> SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');
    -> '2013-05-01'

See the documentation on this function.