SQL order by dates

[swamp]

Hi there,

 

I have dates in this format in my database:

 

dd.mm.yy

 

So today would be 24.02.09

 

I know this may not be the correct way of entering them but they have been transfered like this and there are 6000+ entries.

 

How could I order them by date descending and leave them in the current format?

 

Thanks in advance!

[premiso]

I do not think you can....that is just a bad designed DB.

 

MySQL Date Functions

 

About all I could think is you can try the MakeTime function, or the STR_TO_DATE function and see if they allow you to convert it to a good date format to sort by.

 

 

To fix this issue at it's source, you should be able to create a new date column, which will store a new date and be of DATETIME and use that str_to_date function to convert the current date to a good date and update it.

 

If that does not work, you can pull it out in php explode the date at the "." then re-create it in a good date format and input it back into the DB so you have the correct format and not have to do this "hack" to get your sql to sort by date.

[cooldude832]

Is it a varchar or text or integer field?

 

 

as stated above bad practice dates should be in YYYYMMDD HH:MM:SS format or the native date/time format for that language

 

Fix all the dates via a php query and update and you will solve this issue and many future ones

[swamp]

Thanks for your help guys,

 

I realise that it isn't the right way to do this but it is what I've been supplied with!

 

Cheers!