CODEWITHSUNDEEP
phpmysqlsqldatedate-math
passatgt
passatgt

Reputation: 4442

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.

Upvotes: 1

Views: 843

Answers (1)

John Conde
John Conde

Reputation: 219924

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()

Upvotes: 5

Related Questions