[SOLVED] help with date sorting.

[jaikob]

okay, I have a varchar field in mysql, in order to store dates in the form of: 1/1/08. No leading zeros in the single digits. To sort the varchar field by date, Im trying to use STR_TO_DATE function, but It will not work. Here is my sql:

 

SELECT *, STR_TO_DATE(date, '%m/%d/%y') AS date_sort

FROM publications

WHERE type = 'News Letter' AND archive = 'Active'

ORDER BY date_sort DESC

 

I get 00-00-000 Down the date row when I run the query. How do I get this to work?

[AjBaz100]

Not sure but there could be a problem with your implementation of STR_TO_DATE

 

If you do a select without the STR_TO_DATE function, what is the result set.

 

i.e

SELECT date

FROM publications

WHERE type = 'News Letter' AND archive = 'Active'

ORDER BY date DESC

 

 

[jaikob]

Update, I have changed all of my dates to MM/DD/YYYY format. But STR_TO_DATE still sorts by the DD first, then the YYYY Part.

 

So it looks like this:

 

01/26/2006

02/26/2006

02/27/2007

02/28/2007/

03/01/2006

[jaikob]

Not sure but there could be a problem with your implementation of STR_TO_DATE

 

If you do a select without the STR_TO_DATE function, what is the result set.

 

i.e

SELECT date

FROM publications

WHERE type = 'News Letter' AND archive = 'Active'

ORDER BY date DESC

 

 

 

it sorts it by the first few #'s. Not the actual year.

It is a varchar field by the way.

[jaikob]

Is there anyway I can update the whole table using STR_TO_DATE?

 

for example:

 

UPDATE publications, STR_TO_DATE('date', '%m/%d/%Y') AS date_sort

SET date=date_sort

 

that doesn't work though.

[AjBaz100]

execute this:

 

SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');

 

 

Does it return a valid date.?

[jaikob]

yes.

[jaikob]

I fixed it myself.

 

 

Note to Self: Do not use empty fields with mysql functions.

 

Thanks!

[fenway]

I fixed it myself.

 

 

Note to Self: Do not use empty fields with mysql functions.

 

Thanks!

I really hope you didn't change this field from a DATE column.