How can I create a table with a column in Date dd / mm / yyyy format and sort it later with ORDER BY?

  • "How can I create a table with a column in the format of a Date?" Is very simple. You need to create a column with type DATE . Details are in the documentation . - PinkTux
  • The problem is that there is a yyyy / mm / dd format and I need dd / mm / yyyy - max
  • one
    you should not care how the date is stored inside the database. Sorting will work in any case, and you can display the values ​​in any format as you wish. Try to still read the documentation (and it is better to do this before asking such questions ...) - PinkTux
  • But the problem is when I do sorting through ORDER BY, I do not get the desired result, namely, for example, I have the date 15/3/2016 and 15/2/2016, I want to see the date 15/2/2016 first, and I see 15/3/2016 - max
  • That is, the advice to read the documentation you basically ignore .... - PinkTux

1 answer 1

There is no need to interfere with the ways to store the date in the database. The base itself will figure out how it is more convenient for her to do it. And if we are talking about the presentation of the date on the user side, then there are functions for working with dates .

Example:

 DROP TABLE IF EXISTS datetest; CREATE TABLE datetest ( td DATE ); INSERT INTO datetest (td) VALUES (STR_TO_DATE('15/3/2016','%d/%c/%Y')), (STR_TO_DATE('15/2/2016','%d/%c/%Y'));

Sample times:

 SELECT DATE_FORMAT(td, '%d/%c/%Y') FROM datetest ORDER BY td;

Result:

 15/2/2016 15/3/2016

Sample two:

 SELECT DATE_FORMAT(td, '%d/%c/%Y') FROM datetest ORDER BY td DESC;

Result:

 15/3/2016 15/2/2016
  • Thank you ........ - max