mysql Order by date DESC

soupa

Hello All,

I have entries in a mysql db.
the date is stored in the following format
yyyy-mm-dd

What I am attempting to do is select all the entries and order them by date in descending order.

What is happening is hat it's ordering it by date in descending order but is not taking into account the year.

Only the mm and dd.

Everything looks fine until the year 2003 is entered with a adate. that entry gets listed at the bottom of the query.

Thanks for any help

jburfield

This may be a stupid question, but the field in the database that is holding the date is indeed a 'date' field type, correct?

It is is not, then that is your problem. If it IS, then can you post your query so we can take a look?

-- Jason

soupa

$select_query="SELECT cust_id,DATE_FORMAT(startdate, '%m-%d-%Y')AS startdate,DATE_FORMAT(postdate, '%m-%d-%Y')AS postdate,zipcode from drental ORDER BY startdate DESC

This is what the query looks like.

Again it does sort in descending order, but when an entry with the year 2003 is entered it gets sent to the end of the entire query. Instead of being on top.

Thanks

jburfield

Try adding this:

$select_query="SELECT cust_id,DATE_FORMAT(startdate, '%m-%d-%Y')AS startdate,DATE_FORMAT(postdate, '%m-%d-%Y')AS postdate,zipcode, startdate as sortdate from drental ORDER BY sortdate DESC;

See if that gets you the proper results. I have had problems in the past when added a 'date_format' to a query that it fails to sort that way I think it should. My work-around has alway been to select the raw date and sort on that. It has always worked so far.

See if it works for you.

-- Jason

hoojx

I always use a timestamp (INT,10) to store date.time in the database. Is this good or bad ?

soupa

jburfield Thanks your solution worked perfectly.

Thank You

jburfield

soupa...excellent. Glad to help.

hoojx: timestamp columns will always get updated any time a record is modified in your database...check the MySQL docs on that one.

I personally prefer datetime fields myself.

-- Jason