[SOLVED] sorting date format

[williejoe]

I have an output that is a date in the format like this: Nov 23, 2007 which comes from my MySQL database and is formatted using my recordset.  I would like to sort the list of dates with newest first, so I put a line in my recordset that says: ORDER BY 'date' DESC

The problem is that it sorts on the first letter of the month and not the actual date.  How can I fix this?

[PFMaBiSmAd]

The only effective way to sort or compare dates is if they are in the form of most significant field to least significant field - ie. year then month then day or if the are converted to a form where later dates have higher values, such as a Unix timestamp. This is why the standard sql DATE data type is yyyy-mm-dd.

 

You need to store your dates in a database as a DATE data type to allow sorts and comparisons to directly work.

[williejoe]

The 'date' column in my database is formatted like this: 2007-12-25 and in my recordset I have this:

DATE_FORMAT(date, '%M %e, %Y') AS date

which formats the output into this format: Dec 25, 2007.

I could change the DATE_FORMAT line so that the output was year-month-day, but I would rather leave it the other way because I think it is easier to read.  Is there a way to format the output so I can sort it or is there a way to alter the recordset to get this? 

[PFMaBiSmAd]

Your ORDER BY is apparently operating on your formatted output with the alias name date instead of your column named date (I would think this would generate an error because the reference is ambiguous.) I recommend picking an alias name that is different than a column name.

 

Once you get your query to ORDER BY your column named date, it should work the way you expect.

[williejoe]

Are you saying I need to do something like this:

 

DATE_FORMAT(date, '%M %e, %Y') AS newdate

 

without changing anything else, and then the sort should work?

[PFMaBiSmAd]

Yes.

[mysty]

SELECT jobid, person, email, phone, company, position, city, state, description, 
DATE_FORMAT(date, '%M %e, %Y') AS newdate
FROM job_entry
ORDER BY `date` DESC

 

This is the entire recordset.  When I use it as-is, the date column is blank.

[fenway]

Blank? How do you know, you're not getting it back?

[PFMaBiSmAd]

Unless williejoe and mysty are the same person, the problem occurring in mysty's code may or may not have anything to do with the question and problem being addressed in this thread.

[williejoe]

That's my cousin trying to be helpful.  I think I found my problem without him.  I forgot to tie the date column to the new dynamic field.  That is, I had the date column tied to 'date' and not 'newdate'.  Thanks guys, and (cousin) Roger, I didn't need you after all.