Request by date

Asked

Viewed 357 times

1

How can I order a query by date?

SELECT * FROM agenda ORDER BY evento ASC

Thus it is appearing as follows: 30/03/2017, 31/05/2016 (the date with the year 2016 should appear first).

php mysql
  • 1

    Date is varchar? convert it to date at the time of ordering. Related: Varchar or Datetime?

  • @rray, I did it the way juniorb2ss suggested, it all worked out. Thank you.

1 answer

5


William, to be able to sort by date the field evento it is necessary to be with the type datetime.

If the field is of the string type, you will need to convert this date to a date instance and thus perform manipulation.

SELECT * FROM agenda ORDER BY STR_TO_DATE(evento, '%d/%m/%Y') ASC

Reference:

STR_TO_DATE(str,format) STR_TO_DATE (str, format) This is the inverse of the DATE_FORMAT() function. It takes a str string and a string format. STR_TO_DATE() returns a DATETIME value if the format character sequence contains parts of date and time, or a DATE or TIME value if the character sequence contains only parts of date or time. If the date, time or date value / time extracted from str is illegal, STR_TO_DATE() returns NULL and produces a warning.
https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

Suggestion

It is recommended that the field case evento be your type varchar() you switch to the type datetime, because it is the right way and you will have many features available in Mysql to work with this field.

See more details in this other answer: Varchar or Datetime?

  • 2

    Not only "much more functionality" but DATETIME will perform much better. The STR_TO_DATE() will be made every call on every line, it is insane to use this. The best solution is to pass to DATETIME or TIMESTAMP or at most INT.

  • @Inkeliz very well placed.

Browser other questions tagged php mysql

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