[RESOLVED] Order by string converted to date

Double_M

Hey all,

im trying to sort a query like this:

SELECT * FROM TABLE WHERE THIS=THAT ORDER BY DATE DESC;

but the date column is a string like 01/01/2007 for example.

so i have tried this:

SELECT * FROM TABLE WHERE THIS=THAT ORDER BY DATE_FORMAT(DATE, '%Y-%m-%d') DESC;

i have tried the above sql statement but it doesnt seem to do anything. Could anyone help me out here. Just to order a query using a string converted to a date within the statement.

Thanks for any help.

NogDog

If you're saying that the column "DATE" is character type (e.g.: varchar) with the value representing a date in the format "mm/dd/yyyy", then:

. . . ORDER BY STR_TO_DATE(DATE, '%m/%d/%Y')

This, of course, points to why it would probably be better to use a DATE type of column instead of a character type to store dates.

Double_M

That worked out great thanks alot for your help🙂

bradgrafelman

As NogDog points out, is there any reason why this isn't a DATE column in the first place?

Anywho... don't forget to mark this thread resolved.

Double_M

Just due to it be a large system and using varchar throughout and it is much quicker to use the above method than change everything else to accomdate this one thing.

Sorry i forgot to mark as resolved. Thanks again.