Order articles by month

Anon

I have a database with news articles in it,

When the stories are inserted in the database
The date record of the news article gets the following value:

echo date("F d, Y");

What I want is to get an overview of all the articles by month.

e.g. show all the stories of the month december.

How can I get this out of the (Mysql) database?

twmcmahan

You should insert the date into the database field "datetime" with the following format:

echo date("Y-m-d H:i:s");

then you can do selects in mysql like:

select * from articles where submitdate >= '2001-05-01 00:00:00' && submitdate <= '2001-05-31 23:59:59';

Tim
Pay for what you Use, not what you don't.
http://www.Tier1Host.net

Anon

Thanks for your reaction.

The problem is; I have stored the dates in a varchar(20) field. (and there are now 4495 articles already in the database).

Do you have any suggestions to convert these dates to values so I can run queries like:

select * from articles where submitdate >= '2001-05-01' && submitdate <= '2001-05-31';

Kind regards,
Marco

Ps.: I already tried to change varchar to date in the mysql admin but because the dates are stored in the format:
echo date("F d, Y"); "May 06, 2001"
it doesn't covert well I get 0000-00-00 in all felds (of course I tried this on a copy of the table)

twmcmahan

Create a different table with the correct set up... write a script to read the date in... split the date up, putting it into the correct format, then write it to the new table.. when the new table is correctly written, rename the table to the name of the old table. You should be set.