MYSQL How to put this date field in order

Navigation
3

I have a date campom with all my date separated and I need to put in order as I do..get example that is not working ... 

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

It leaves in order but not in the right order date fields are formatted as 2 digits in numbers, type 06 - 04 - 2014

    
asked by anonymous 07.04.2014 / 03:26

3 answers

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

I believe doing the join with Concat , transforming on date with Date resolves your problem ...

References

07.04.2014 / 03:44
1

For you to use dates in the database, the correct one is to have a field in datetime () format ( link ) and then you will be able to sort by date. Otherwise, your field will be treated as a string or int (depending on what shape the bank fields are).

In PHP you can easily convert a date format field to save correctly to the database using the date () function ( link )

    
07.04.2014 / 03:43
0

I believe converting these columns to a date field with str_to_date () solve the sort problem: 

SELECT str_to_date(concat(ano,'-',mes,'-', dia), '%Y-%m-%d') as nova_data FROM datas 
ORDER BY nova_data ASC
    
07.04.2014 / 03:48
Displaying places near the current location of the Android Device Error in encryption in C #