Using STR_TO_DATE to sort.

[pwnuspoints]

Hey phpfreaks!

 

As always; your help is appriciated- Thanks in advance!

 

I have several dates housed as text strings in a table. Each date is written in date("F j, Y") format [e.g. "January 28, 2009"].

When I display those dates on a php page, it has a tendancy to order them alphabetically-- which I find confusing. So, I'm trying to use the STR_TO_DATE sql function to display these strings in by date; it's not working.

 

My query looks like this:

 

$result = mysql_query("SELECT * FROM table WHERE display LIKE '1' ORDER BY STR_TO_DATE('createdate', 'F j, Y') DESC ");

 

What am I doing wrong?

 

Thanks Again.

[Sudden]

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date

 

Might help you out.

[severndigital]

agreed it is a syntax issue just take the quotes out of your first parameter and it should work.

[pwnuspoints]

My query now reads as follows:

 

$result = mysql_query("SELECT * FROM table WHERE display LIKE '1' ORDER BY STR_TO_DATE(createdate, '%F %j, %Y') ASC ");

 

>.> it appears to be ordering them by my primary key instead of by date. This is so frustrating.

 

Is what I'm trying to accomplish even possible?

[PFMaBiSmAd]

'%F %j, %Y' is not correct. Based on reading the page in the mysql manual, it should be '%M %e, %Y' or '%M %d, %Y' depending on if your days have leading zeros.

 

Doing your query this way is exceedingly slow and the current format you are using takes several times the amount of storage that using a DATE data type would. You should convert to use a DATE data type.