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.
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.
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. 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');
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
SELECT * FROM 'mensagens' order by date(concat(ano,'-', mes,'-',dia)) asc
So, suit your model