Need to retrieve orders from database based on below condition-CodePudding

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}

CodePudding user response：

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.

CodePudding user response：

Using the OUTPUT command you will capture the rows you are deleting.

Like this:

USE [tempdb]

IF OBJECT_ID('Orders') IS NOT NULL DROP TABLE Orders
CREATE TABLE Orders (ID int, orderCreationDate datetime)
INSERT INTO Orders values (1, GETDATE()), (2, DATEADD(MONTH, -1, GETDATE())), (3, DATEADD(MONTH, -2, GETDATE())), (4, DATEADD(MONTH, -3, GETDATE())), (5, DATEADD(MONTH, -4, GETDATE()))

SELECT * FROM Orders

IF OBJECT_ID('OrdersDeleted') IS NOT NULL DROP TABLE OrdersDeleted
CREATE TABLE OrdersDeleted (ID int, orderCreationDate datetime)

DELETE Orders OUTPUT deleted.* INTO OrdersDeleted WHERE DATEADD(MONTH, 3, orderCreationDate) > GETDATE() 

SELECT * FROM OrdersDeleted