mysqldateintervalsrowdeleting

Deleting records that are over 4 years old from today's date on MySQL

I want to delete records from my table when an end-date column is over 3 years expired past the current date

See below for what i have already tried

DELETE FROM membership
WHERE (SELECT EXTRACT (YEAR FROM end_date)) <
(SELECT EXTRACT (YEAR FROM (SELECT DATE_ADD( CURDATE(), INTERVAL -4 YEAR))))

I expect results that are from 2016 and before that to be deleted in my table

Thanks in advance

Solution

  • This will delete everything that has an end_date with a year of 2016 or older:

    DELETE FROM membership WHERE YEAR(end_date) <= YEAR(CURDATE() - INTERVAL 3 YEAR);

    EDIT: If you want to delete everything with an end_date that is over 3 years old:

    DELETE FROM membership WHERE end_date < NOW() - INTERVAL 3 YEAR;