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

Question

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.

How do you know there are records in the future? – Dan Bracuk Nov 01 '13 at 14:10 — Dan Bracuk, Nov 01 '13 at 14:10
Store your dates properly. – Strawberry Nov 01 '13 at 17:08 — Strawberry, Nov 01 '13 at 17:08

score 5 · Accepted Answer · answered Nov 01 '13 at 14:06

5

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

answered Nov 01 '13 at 14:06

John Conde

1

That is true. But this should point them in the right direction. – John Conde Nov 01 '13 at 14:08
Yes, it was a simple string, works like a charm now! – passatgt Nov 01 '13 at 14:09

1 Answers1