Sorting a date field

[Anon]

Hi,

I use a mysql database to manage a list of posts. To do this is use a table with a column of the DATE type. My question is how do i sort on this date column ?
The date format should be dd-mm-yyyy, so i tried to use a "select datecol DATE_FORMAT(datcol, '%d-%m-%Y') as datecol ORDER BY datecol".
But that only sorts on the day-part, so how do i get a sorted list of dates from mysql ?

Thanks in advance

[jblallement]

Hi,

Your query sort rows on the result of the expression DATE_FORMAT(datcol, '%d-%m-%Y'), that is alphabetical order.

Did you just try to do:

select datecol DATE_FORMAT(datcol, '%d-%m-%Y') as datecol
FROM ....
ORDER BY datcol -- ( datcol without 'e' )

Hth

[Anon]

Thx man,

I didn't know it worked like that...
thx a lot...

Nick

[Anon]

select DATE_FORMAT(datum,'%d-%m-%Y') as datok from nieuws order by datok ASC");

works just fine, but the same command on the table docmain instead of nieuws doesn't sort my dates ;( I can't figure out why... but maybe someone can 🙂

Table nieuws:
mysql> show columns from nieuws;
| id | smallint(5) unsigned |

| datum | date | YES

| titel | varchar(100) | YES

| omschrijving | text | YES

| linktekst | varchar(50) | YES

| url | varchar(50) | YES

mysql> show columns from docmain;
| id | smallint(5) unsigned | | titel | varchar(100) | | datum | date | YES | dl1 | enum('Y','N') | YES | dl2 | enum('Y','N') | YES | dl3 | enum('Y','N') | YES | omschrijving | text | YES

=> You see i allmost used the same columns but it doesn't sort for table docmain :

mysql> select DATE_FORMAT(datum,'%d-%m-%Y') as datok from nieuws order by datok ASC;
+------------+
| datok |
+------------+
| 02-01-2001 |
| 08-01-2001 |
| 20-07-2000 |
| 23-12-2000 |
| 29-11-1999 |
+------------+
5 rows in set (0.00 sec)

BUT

mysql> select DATE_FORMAT(datum,'%d-%m-%Y') as datok from docmain order by datok ASC;
+------------+
| datok |
+------------+
| 05-10-1998 |
| 10-07-2000 |
| 29-11-1999 |
| 30-09-1998 |
+------------+
4 rows in set (0.01 sec)

So if someone might help me out again :-)

Nick

[krisha]

to see columns

show columns from table; ir longs to write

use desc table;

[Anon]

Hmm,

I'm afraid I don't really get what you mean :-(

I just don't get what the difference between my two tables is, the one does sort on date, the other doesn't...

[[deleted]]

select DATE_FORMAT(datum,'%d-%m-%Y') as datok from nieuws order by datok ASC");

Like JB said, you should not order by "datok", you should order by "datum"

[Anon]

ok thanks,
my mistake :-)

It works great now...

Nick