如果加入日期为六个月，则从表中删除记录

Question

I want to delete a record from table if the field joindate is six months old or older.

Suppose An employee's joindate date is 15-06-2018 and on 15-11-2018 the user should be deleted

I have stored joindate and datetime. now trying to create a query that will delete a record if current date equals joindate + 6month.

Answer 1

Seems like a pretty simple expression.

joindate < DATE(NOW()) + INTERVAL -6 MONTH

take current date, subtract 6 months from it, and then compare to the joindate column, to find out if joindate is earlier.

We can test that in a query:

SELECT t.joindate < DATE(NOW()) + INTERVAL -6 MONTH `older_than_six_months`
     , t.joindate 
     , t.id
     , ... 
  FROM mytable t
 ORDER BY t.joindate ASC

The expression will return 1 (true) if joindate is earlier than six months, or return 0 (false) or NULL.

We can specify a specific date value in place of DATE(NOW()) for testing

SELECT t.joindate < DATE(NOW()) + INTERVAL -6 MONTH `older_than_six_months`
     , t.joindate < '2018-07-01' + INTERVAL -6 MONTH `six_months_20180701`
     , t.joindate 
     , t.id
     , ... 
  FROM mytable t
 ORDER BY t.joindate ASC

Once we have an expression that is tested, we can use it in a DELETE statement

DELETE t.*
  FROM mytable t
 WHERE t.joindate < DATE(NOW()) + INTERVAL -6 MONTH 

(Personally, I would do the less than or less than or equal comparison test < or <= rather than an equals test.)