CODEWITHSUNDEEP
mysqldateintervalsrowdeleting
Gary Mannion
Gary Mannion

Reputation: 29

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

Upvotes: 0

Views: 3183

Answers (2)

spencer7593
spencer7593

Reputation: 108400

Some notes:

  • compare the bare column end_date against a value, as a date type
  • test expressions in SELECT statements, before running DELETE

What is the first date value that is greater than the end_date on the rows you want to delete? Test that expression in a SELECT statement.

SELECT DATE_FORMAT( NOW(), '%Y-01-01') + INTERVAL -3 YEAR

returns

2016-01-01

or, date value four years before now 

SELECT DATE(NOW()) + INTERVAL -4 YEAR

returns: 

2015-04-05

adjust that expression until it returns the value we need. Then we can include that expression in a statement, comparing to bare column end_date column, like this:

SELECT m.* 
  FROM membership m 
 WHERE m.end_date < DATE(NOW()) + INTERVAL -4 YEAR
 ORDER BY m.end_date DESC

or if we know that the date value we need is '2015-04-05' or '2017-01-01' then we can just specify that as a literal:

SELECT m.* 
  FROM membership m 
 WHERE m.end_date < '2017-01-01' + INTERVAL 0 MONTH
 ORDER BY m.end_date DESC

After we confirm that the SELECT statement is returning the set of rows we want to delete, then we can replace the SELECT keyword with DELETE.

Upvotes: 0

derek.wolfe
derek.wolfe

Reputation: 1116

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;

Upvotes: 3

Related Questions