[RESOLVED] Converting months to numbers

blaster_master

I have a db full of dates in the format of "September 1 2007", built using the following command:

$currentdate["month"]." ".$currentdate["mday"]." ".$currentdate["year"]

In order to sort these dates I think they need to be converted to the numerical form of 1/9/2007 or something like that. I can't figure out what's the best way of doing it.

Any help is greatly appreciated.

bradgrafelman

Why not store the dates in a logical format for datetime handling, such as a DATE field?

blaster_master

The dates are already in the db.

Let me explain more: Like I said my db has hundreds of entries with dates in it (i.e. for each entry there's a column called 'date'). These dates are in the format of September 1 2007. I want to write a code that allows the user to remove all the entries older than a target 'date.' The only way I can think of is first get all dates in the numerical form and then compare them one by one to the target date; if it's older then the entry is removed... Even that, I don't know how to do.

bradgrafelman

The best thing to do would be to convert the entries in the DB and use a proper DATE format; it'd be much easier and much more effective that way.

Otherwise, you'll have to generate datetime expressions MySQL can understand during each query, like so:

SELECT myDate WHERE STR_TO_DATE(myDate, '%M %e %Y') <= '2005/1/10'

where the 2005/1/10 expression is an example target date.

NogDog

If using MySQL, you can use its STR_TO_DATE() function, something like:

$sql = "DELETE FROM table_name WHERE STR_TO_DATE(date_field, '%M %e %Y') < '$someDate'";

(Where $someDate is a valid MySQL date string, usually in "yyyy-mm-dd" format.)

blaster_master

Thanks for all the replies.

I use Postgresql for my db. I looked up STR_TO_DATE on their website and apparently "to_date" is the equivalent command in Postgres. But I can't get the comparison to work correctly. I used something like this (and other variations):

select * from web_log where to_date(date, 'DD MM YYYY')>'2 6 2006';

Installer

Did you try "...where to_date(date, 'Month DD YYYY')>'20060206'" or "...where to_date(date, 'FMMonth FMDD YYYY')>'20060206'"?

blaster_master

Installer wrote:
Did you try "...where to_date(date, 'Month DD YYYY')>'20060206'" or "...where to_date(date, 'FMMonth FMDD YYYY')>'20060206'"?

Superb! Thank you, the second format worked (with FM)!