MYSQL How to order this date field

Question

I have a date field with all separate date and need to put in order as I do.. follows example that this not working...

$query = mysql_query("SELECT * FROM `mensagens` ORDER BY `ano`,`mes`,`dia` ASC") or die(mysql_error());

he leaves in order but not in the right order date fields are 2-digit formatting in numbers, type 06 - 04 - 2014

Answer 1

SELECT * FROM `mensagens` order by date(concat(ano,'-', mes,'-',dia)) asc

I believe making the junction with Concat, turning into date with the function Date solves your problem ...

References

• Date and Time Functions

• String Functions

Answer 2

To use dates in a database the correct is to have a field in the datetime format() (https://dev.mysql.com/doc/refman/5.1/en/datetime.html) and then you’ll be able to do the ordination by date. Otherwise, your field will be treated as string or int (depending on how the database fields are).

In PHP you can easily convert a field into date format to save correctly in the database using the date function() (http://www.php.net/manual/en/function.date.php)

Answer 3

I believe that convert these columns to a date field with str_to_date() solve the sorting problem:

SELECT str_to_date(concat(ano,'-',mes,'-', dia), '%Y-%m-%d') as nova_data FROM datas 
ORDER BY nova_data ASC