CODEWITHSUNDEEP
mysql
Isuru Sandamal
Isuru Sandamal

Reputation: 536

How to delete rows older than some year?

I want delete rows from my database which is older than 4 year.this is my query.Is tat correect?Here "$year" is the year from my database table.

"DELETE FROM undergraudate WHERE YEAR(GETDATE())-'$year'>4"

Upvotes: 0

Views: 1796

Answers (2)

Rahul
Rahul

Reputation: 77846

Yes, it's looks fine but most probably you have datetime or date field in your database and so do like below. Again, GETDATE() is a SQL Server function. I think you meant CURDATE() or NOW()

DELETE FROM undergraudate 
WHERE YEAR(CURDATE()) - YEAR(your-date-field) > 4

Upvotes: 0

SMA
SMA

Reputation: 37023

Your query won't work if today's date is 09th Jan 2016 and your date in table is 10th Nov 2012. So better you could do something like:

DELETE FROM undergraudate 
WHERE year > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1461 DAY))

Upvotes: 2

Related Questions