comparing dates in mysql database

PaulStat · 29 Sep 2010 at 12:58

Ok the scenario is I have a column in one of my tables that has a date in the format DD/MM/YYYY, what I want to do is delete all rows from that table where this column is less than the current day (i.e. in the past).

How do I do this in SQL? (MySQL)

Pho · 29 Sep 2010 at 13:16

Is the column set as a DATE type or just plain text? I'd get it converted into DATE if it isn't.

You should then be able to do something like:

PHP:

SELECT * FROM TABLE WHERE DATE < NOW()

Yakyb · 29 Sep 2010 at 14:32

if it is text you should be able to cast it to a date

PaulStat · 29 Sep 2010 at 15:42

yeah I think I'll try formatting the date into the correct format before inserting it into the table. Thanks

helium · 30 Sep 2010 at 12:53

You should store the date as a unix timestamp, very flexible from there.