Search code examples
phpmysqlsqldatedate-math

Delete every record from mysql table where the date field is NOT in the future

I want to delete every row from the database where the Submission field is not in the future. The date is stored this way:

09/10/2013

So, in this example i want to delete this record, because its already expired. This is where i am:

$wpdb->query("DELETE FROM `$table_name` WHERE `Submission` < NOW()");

Looks fine for me, but this query deletes everything from the table, not just rows in the past.

Solution

  • That date is not a valid datetime. It's a string. You need to use STR_TO_DATE() to convert it into a date before doing date math on it.

    DELETE FROM `$table_name` WHERE STR_TO_DATE(`Submission`, '%m/%d/%Y') < NOW()