0

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}

2 Answers2

0

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.

Jonas Metzler
  • 4,517
  • 1
  • 5
  • 17
  • Please upvote the question if you find it can be useful for others as well :) –  Dec 07 '22 at 07:43
-1

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
johndk
  • 57
  • 4