PHP and MySQL Sort Date

Question

I've got a question about sorting the MySQL output in a column where my date ist stored. The format is "10.01.2014 - 16:01 - Fr.".

I've got a query is as followed:

SELECT * FROM `jtmpl2_chronoforms_data_test_reservierung` ORDER BY `res_date` ASC

The problem is, that it only orders the date with the first number. So for example "10.01.2014 - xxxx" is before "12.12.2013 - xxxx".

Any idea how i can solve this?

Use the mysql datetime field YYYY-MM-DD or you are in for lots of problems. — AbraCadaver
– AbraCadaver, Commented Dec 10, 2013 at 16:03
John's point is that there are specific data types for dates so that you can sort them properly; you can then use formatting commands in either mysql or php to display the date in the form you want. If your source data is storing the date as a string, you should use an ETL script to convert it to a datetime. — outis nihil
– outis nihil, Commented Dec 10, 2013 at 16:04
Store dates in the mysql date format. If you can't anymore, you still can think about sorting them through PHP using asort after querying them, even though it is NOT a really valuable solution. — briosheje
– briosheje, Commented Dec 10, 2013 at 16:06

Ryan · Accepted Answer · 2013-12-10 16:06:21Z

2

You can use the STR_TO_DATE function to convert from string to a true date representation.

See http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_str-to-date

Something like this could you work (but you may need to play with the format):

SELECT * 
FROM jtmpl2_chronoforms_data_test_reservierung
ORDER BY STR_TO_DATE(res_date, '%M.%d.%Y %h:%i') ASC

The format string above is only a suggestion - be sure to check that you have a correct conversion from the string to the actual date it should represent.

answered Dec 10, 2013 at 16:06

Ryan

28.4k10 gold badges58 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

eScoo Over a year ago

This solution works for the date, but not the time. I've tried STR_TO_DATE(res_date, '%d.%m.%Y - %h:%i'), but it does not work. Do you have any solution for this?

Ryan Over a year ago

Played with the format specifiers, and got the following to work: select str_to_date('10.01.2014 - 16:01 - Fr', '%m.%d.%Y - %H:%i' );. For the specifiers, see dev.mysql.com/doc/refman/5.0/en/….

John Conde · Accepted Answer · 2013-12-10 16:39:39Z

0

SELECT * FROM `jtmpl2_chronoforms_data_test_reservierung` ORDER BY DATE(`res_date`) ASC

|| 

SELECT * FROM `jtmpl2_chronoforms_data_test_reservierung` ORDER BY DATE_FORMAT(`res_date`,'%Y%m%d') ASC

edited Dec 10, 2013 at 16:39

John Conde

220k100 gold badges464 silver badges504 bronze badges

answered Dec 10, 2013 at 16:13

Carlos Avalos

2474 silver badges8 bronze badges

Collectives™ on Stack Overflow

PHP and MySQL Sort Date

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related