Need to retrieve orders from database based on below condition

Question

I am writing a Flexible Search query to get the orders from database and delete them. The condition to get the orders is that : if the order is placed 3 months before the current date, I need those orders to be deleted from db. Example: currentDate: 12/07/2022; orderCreationDate: 01/06/2022. As this orderCreationDate is 3 months older than currentDate, I need to fetch this order. Can you please help me modify this query accordingly?

select {code},{creationtime},{date} from {order} order by {date}

Answer 1

We can use DATE_SUB:

DELETE FROM orders
WHERE orderDate <= 
  DATE_SUB(CURDATE(), INTERVAL 3 MONTH);

See the documentation

Try out: db<>fiddle

Note: If you want to do today's good deed, you should rename your table name "order" and your column name "date" (as I did in my command).

It's recommended to avoid using SQL key words as table names or column names if possible.