How to order a MySQL search with date in d-m-Y format?

5

The date in my database is saved as dia-mês-ano . I would like to know how I can sort by date using this date format.

    
asked by anonymous 08.04.2014 / 17:07

4 answers

6

If data is stored in string (char or varchar field), there may not be any need for conversions just for sorting, so you can simply use substrings, which are quick to process:

If you have a date separator (dd-mm-yyyy), it looks like this:

SELECT * FROM basededados
   ORDER BY 
      SUBSTR( campoComAData, 7, 4), 
      SUBSTR( campoComAData, 4, 2),
      SUBSTR( campoComAData, 1, 2)

If it is without tabs (ddmmaaaa), just adjust the indexes:

SELECT * FROM basededados
   ORDER BY
      SUBSTR( campoComAData, 5, 4), 
      SUBSTR( campoComAData, 3, 2),
      SUBSTR( campoComAData, 1, 2)

Basically, we are saying via ORDER BY : "order by Year, then by Month, and finally by Day."

  • SUBSTR( string, inicio, qtd) extracts the chunk of the string starting with "start" and picking "qtd" characters.
08.04.2014 / 17:16
5

If the date is stored as text, you can use the STR_TO_DATE of MySQL:

SELECT data 
FROM tabela
ORDER BY STR_TO_DATE(data, '%d-%m-%Y');
    
08.04.2014 / 17:22
2

Just add the format with date_format () in the field list and then order:

SELECT date_format(data, '%d/%m/%Y') FROM datas ORDER BY data DESC
    
08.04.2014 / 17:11
1
SELECT * FROM 'mensagens' order by date(concat(ano,'-', mes,'-',dia)) asc

So, suit your model

    
08.04.2014 / 17:10