I need a query to delete a row if a date is expired

Question

Here is my query:

DELETE FROM `upcoming` WHERE `Date` < DATE_FORMAT(CURDATE(), '%M-%d-%y')

When I run it, it deletes every thing in my table. Date is the column where I put the upcoming event dates. This is what i have in my table. It should only delete the one date not both of them.

Table:

12-25-13
11-11-13

Todays date:

11-25-13

If your dates are formatted mm-dd-yy, why is your query using mm/dd/yy? — , Nov 26 '13 at 01:16
This might be helpful: [Deleting All Rows Older Than 5 Days](http://stackoverflow.com/questions/5853543/deleting-all-rows-older-than-5-days) — showdev, Nov 26 '13 at 01:18

score 0 · Answer 1 · answered Nov 26 '13 at 02:01

0

Try

DELETE FROM `upcoming` WHERE STR_TO_DATE(`Date`, '%M-%d-%y') < DATE_FORMAT(CURDATE(), '%M-%d-%y')

answered Nov 26 '13 at 02:01

Lohardt

1,057
1
12
26

spencer7593 · Answer 2 · 2013-11-26T03:16:46.090

The normative pattern is to use DATE datatype to store date values.

It appears you are storing your "date" values as VARCHAR, in format 'mm-dd-yy'. Comparisons of VARCHAR expressions is performed character by character, from left to right. The character representation, with the month first, is not in canonical order, that is, a sort of the mm-dd-yy values will not be sorted in date order, e.g. '01-01-13' (Jan 2013) will sort before '11-27-12' (Nov 2012).

The simplest approach, in terms of a SQL statement, is to convert the VARCHAR into a DATE datatype, and compare that to an expression that also returns a DATE.

DELETE FROM `upcoming` WHERE STR_TO_DATE(`Date`,'%m-%d-%y') < CURDATE()

In terms of performance, MySQL can't perform an index range scan to satisfy this predicate. Every row in the table will need to be examined, to do the conversion, and then the comparison. If the column were stored as a DATE, then the bare column could be referenced in the predicate and MySQL could use an index range scan.

ALSO: '%M' format specifier returns the month name, 'January','February', etc. The '%m' specifier returns numeric month '01','02'.

I need a query to delete a row if a date is expired

2 Answers2