[SOLVED] ordering by date DESC is going by the first Number

[jesushax]

hi heres my sql

 

$PortSQL = mysql_query("SELECT * FROM tblPortfolio WHERE PortCurrent='0' ORDER BY PortDateAdded DESC") or die(mysql_error());

 

when i get my result the dates go like such

 

21/02/08

20/09/07

18/02/08 

17/01/08 

10/11/07

 

so its going 21,20,18,17,10

 

not by actual date, is this becuase my field type is not date?

 

[MadTechie]

is this becuase my field type is not date?

 

Yep

[jesushax]

its changed all my dates to

 

2021-02-08 

2020-09-07

2018-02-08

2017-01-08

2010-11-07

 

and i cant manually change them back

 

does it have to be Year month day? in mysql then you format it with code?

 

 

[MadTechie]

Okay, change it back and try this

 

1. rename the current date field to olddate

2. create a new field called newdate

3. query this (update table to the table name)

UPDATE table SET newdate = STR_TO_DATE(olddate, '%d/%m/%Y');

4. check the values of newdate

 

Note always keep a backup

[jesushax]

ok got that so the newdates are all in the yyyy-mm-dd format

 

and in my php code what do i use to format the date?

 

thanks

[jesushax]

created a function to do it

 

function mydate($record){
$date =  strtotime($record);
echo date('d/m/y',$date);
}

 

[PFMaBiSmAd]

You can use the mysql DATE_FORMAT() function in your SELECT query to format a standard DATE or DATETIME any way you want. No php code is necessary.