mySQL order by date field not working

Question

I am trying to get a data sorted from 2 tables posts and postmeta

Example wp_posts Table

ID      post_title          post_name
328   Test Event Five   test-event-five
326   Test Event Four   test-event-four
324   Test Event Three  test-event-three 
321   Test Event  Two   test-event-two 
320   Test Event One    test-event-one

Example wp_postmeta Table

id  post_id       meta_key                    meta_value
1    328      calendar_start-date            August 08, 2012
2    326      calendar_start-date            August 09, 2012
3    324      calendar_start-date            September 06, 2012
4    321      calendar_start-date            August 23, 2012  
5    320      calendar_start-date            September 17, 2012

In order to output the data ordered by the dates in the meta value, I am using the query

SELECT wp_posts.ID, 
       wp_posts.post_title, 
       wp_posts.post_name, 
       wp_postmeta.post_id,
       wp_postmeta.meta_value as event_date 
FROM wp_posts, wp_postmeta 
WHERE wp_posts.ID = wp_postmeta.post_id 
AND wp_postmeta.meta_key = 'calendar_start-date' 
AND wp_posts.post_status = 'publish' 
AND wp_posts.post_type = 'calendar' 
ORDER BY DATE_FORMAT( event_date, '%M %d, %Y' ) DESC 
LIMIT 0 , 30

But the result is not ordered by the dates, instead it is coming as

ID     post_title          post_name      post_id   event_date
328  Test Event Five    test-event-five     328     August 08, 2012
326  Test Event Four    test-event-four     326     August 09, 2012
324  Test Event Three   test-event-three    324     September 06, 2012
321  Test Event  Two    test-event-two      321     August 23, 2012
320  Test Event One     test-event-one      320     September 17, 2012

Where am I going wrong in the query and how can I display the records ordered properly by the event_date. The input format of the date is MM dd, yy through UI/Datepicker.

Answer 1

The function DATE_FORMAT formats a date to a string. But you want to do the reverse thing. To do that, use the function STR_TO_DATE:

ORDER BY STR_TO_DATE(event_date, '%M %d, %Y') DESC

See http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date for more details on date functions in MySQL.

Answer 2

DATE_FORMAT goes from DATETIME data types to strings. It doesn't grumble when it fails, it just returns NULL. That's why your query is working, even if the ORDER BY is not.

You need to use STR_TO_DATE for what you're trying to do.