I have a table where the dating is not standard and need to somehow organise the rows by date and time.
job_date | job_time
=========================
12/12/2012 | 10.30am
11/10/2012 | 9.00pm
14/11/2012 | 11.50pm
Is there any way of formatting these within mysql. I have looked at the DATE_FORMAT() function but the examples I have found don't seem to relate to the format within my tables.
Obviously storing it as a correct date would be a lot better and result in quicker queries.
SELECT *
FROM table
ORDER BY substr(job_date, -4), substr(job_date, 4, 2), substr(job_date, 1, 2)
SELECT *, STR_TO_DATE(CONCAT(job_date,' ',job_time), 'Y-m-d H:i:s') AS date_format from table ORDER BY date_format DESC
The key method is STR_TO_DATE. I will give you two solutions :
first : if you don't want to change your database :
SELECT jobdate, STR_TO_DATE(job_time,'%h:%i%p')AS real_job_time FROM yourtable ORDER BY real_job_time;
second : if you can modify your database, use the TIME format :
ALTER TABLE yourtable
MODIFY COLUMN job_time TIME NOT NULL;
UPDATE yourtable SET job_time = STR_TO_DATE(job_time,'%h:%i%p');
and to select
SELECT jobdate,job_time FROM yourtable ORDER BY job_time
I think the second solution is by far the best and that you should choose it.