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

Asked

Viewed 5,851 times

5

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

mysql
  • 2

    This is the kind of field date ?

4 answers

6


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

If you have separator on date (dd-mm-yyyy), it can be like this:

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

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

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

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

  • SUBSTR( string, inicio, qtd) extracts the piece of the string starting with "start" and picking up "Qtd" characters.

  • I took a negative vote! D

  • @Miguel Angelo See, it’s not just you that happens.

5

If the date is stored as text, you can use the function STR_TO_DATE mysql:

SELECT data 
FROM tabela
ORDER BY STR_TO_DATE(data, '%d-%m-%Y');

2

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

SELECT date_format(data, '%d/%m/%Y') FROM datas ORDER BY data DESC

1

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

So suit your model

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.