[RESOLVED] Order by date when date not a timestamp

TeamTJ

I have a database of items with 2 fields, LogDate and LogTime.

I would like to be able to display these items ordered newest to oldest.

LogDate is a VarChar(11) and the format of the data is always DD-MMM-YYYY (30-AUG-2006)
LogTime is a VarChar(7) and the format is always HH:MMAP (02:47pm)

How can I order my SQL to sort by those 2 fields as if they were real dates?

bradgrafelman

Try adding this ORDER BY clause to your query:

ORDER BY STR_TO_DATE(CONCAT(LogDate, LogTime), '%d-%b-%Y%h:%i%p') DESC

(Tested and verified on MySQL 5.0.21, compatible with MySQL 4.1.1 and higher due to the STR_TO_DATE() function)

TeamTJ

bradgrafelman wrote:
Try adding this ORDER BY clause to your query:
ORDER BY STR_TO_DATE(CONCAT(LogDate, LogTime), '%d-%b-%Y%h:%i%p') DESC
(Tested and verified on MySQL 5.0.21, compatible with MySQL 4.1.1 and higher due to the STR_TO_DATE() function)

Worked like a charm, thank you!!