[SOLVED] Order by date doesn't seem to work...

[Perfidus]

Hello everyone!

I'm using this query:

SELECT * FROM $table WHERE ref='$ref' ORDER BY DATE(STR_TO_DATE( `thedate`, '%d/%m/%Y %H:%i:%s' )) ASC LIMIT $startv, $amount";

But all the time the results appear in the order given automatically by the primary key which is an autoincrement field not really shorted by date...

Any hints?

[PFMaBiSmAd]

You would need to show what the data is and what the results actually are.

 

My guess is the format string '%d/%m/%Y %H:%i:%s' does not match the actual data (and this is another reason why you should use a DATETIME data type to begin with.)

[fenway]

Yes... show STR_TO_DATE() output first to verify.

[Perfidus]

The data on the cell and cell itself are datetime: 2009-06-14 20:34:20

[Perfidus]

I will try to express myself better:

 

My table has 3 rows:

 

data_id: autoincrement, primary

description: varchar

thedate: datetime

 

This is an example of content:

 

data_id | description | thedate

------------------------------------

1 | whatever info | 2009-06-14 20:34:20

2 | whatever info | 2009-06-12 20:34:20

3 | whatever info | 2009-06-18 20:34:20

 

I want to make a query that returns results from the most recent date to the older one so I'm using:

SELECT * FROM $table WHERE ref='$ref' ORDER BY DATE(STR_TO_DATE( `thedate`, '%d/%m/%Y %H:%i:%s' )) ASC LIMIT $startv, $amount";

But all I get is the results in the order in which they where stored, not shorted by date...

Does something like this exists?

SELECT * FROM $table WHERE ref='$ref' ORDER BY DATETIME(STR_TO_DATETIME( `thedate`, '%d/%m/%Y %H:%i:%s' )) ASC LIMIT $startv, $amount";

If I try it I get an error, I guess it doesn't exist...

Any hints?

[Perfidus]

And this can be the solution:

 

SELECT * FROM $table WHERE ref='$ref' ORDER BY DATE_FORMAT( `thedate`, '%d/%m/%Y %H:%i:%s' ) ASC LIMIT $startv, $amount";

 

At least it seems to work !!

[PFMaBiSmAd]

The column thedate is a DATETIME value, you use it directly in the ORDER BY. If you want the most recent date to the older, use DESC -

 

SELECT * FROM $table WHERE ref='$ref' ORDER BY thedate DESC LIMIT $startv, $amount";