what would be the best way to delete a row from a table based on expiry time

Question

i have a table in which i have two columns named BuyDate and ExpiryDate.
now i need a suggestion what might be the best way to calculate expiry time from the above two columns?
should i create a job to search regularly on every record whether expirydate is reached or passed the current date meaning the package is expired?
or should i be making a logic in asp.net mvc controller action method to check everytime user logins whether the current date and expirydate are same or not and then delete a record if expiry date is reached?
or is there any other better approach to do this.
Basically the need is to delete a record from a table automatically if expirydate has reached or passed.
if i user controller actionmethod then record will not not be deleted until user logs in and that might happen even after expirydate has surpassed
but with sql job each record will be checked on daily basis and will delete a record as soon as expiry date reaches.
waiting for suggestions

EDIT:-
i think buy date doesn't matter in this case..all i need is to check current date and time and compare it with expiry date if current date has passed expiry date then delete the row.
this is the table

USE [TempInvestManage]  
GO  
  
/****** Object:  Table [dbo].[UserPackages]    Script Date: 9/8/2020 9:26:25 PM ******/  
SET ANSI_NULLS ON  
GO  
  
SET QUOTED_IDENTIFIER ON  
GO  
  
CREATE TABLE [dbo].[UserPackages](  
	[Id] [int] IDENTITY(1,1) NOT NULL,  
	[U_ID] [int] NULL,  
	[P_ID] [int] NULL,  
	[PackageStatus] [bit] NULL,  
	[BuyDate] [datetime] NULL,  
	[ExpiryDate] [datetime] NULL,  
 CONSTRAINT [PK_UserPackages] PRIMARY KEY CLUSTERED   
(  
	[Id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]  
) ON [PRIMARY]  
GO  
  
ALTER TABLE [dbo].[UserPackages] ADD  CONSTRAINT [DF_UserPackages_PackageStatus]  DEFAULT (NULL) FOR [PackageStatus]  
GO  
  
ALTER TABLE [dbo].[UserPackages]  WITH CHECK ADD  CONSTRAINT [FK_UserPackage_Packages] FOREIGN KEY([P_ID])  
REFERENCES [dbo].[Packages] ([PID])  
ON DELETE CASCADE  
GO  
  
ALTER TABLE [dbo].[UserPackages] CHECK CONSTRAINT [FK_UserPackage_Packages]  
GO  
  
ALTER TABLE [dbo].[UserPackages]  WITH CHECK ADD  CONSTRAINT [FK_UserPackage_Users] FOREIGN KEY([U_ID])  
REFERENCES [dbo].[Users] ([ID])  
ON DELETE CASCADE  
GO  
  
ALTER TABLE [dbo].[UserPackages] CHECK CONSTRAINT [FK_UserPackage_Users]  
GO  
  
  
  

and here's the sample pic of table

Answer 1

What is the logic to delete the rows, if day difference between Buy and Expire dates more than 20 days then delete the row?
if so
DELETE FROM tbl WHERE DATEDIFF ( day,BuyDate ,ExpiryDate) >20

Answer 2

Add an index on the ExpiryDate column - and use this:

DELETE FROM dbo.UserPackages WHERE ExpiryDate < getdate();

Answer 3

Hi @Shahzaib khan ,

In sql server, if you want to perform an operation automatically, creating a job is a better choice. You can add a column of data to record the user's login status in the UserPackages table (the update of the login status data may require the help of developers), and then you You can use

delete from dbo.UserPackages where ExpiryDate <getdate() and login status='Yes'  

to set the job, so that the expiration date can be deleted after the user logs in. Of course, this is just a suggestion, I can't actually test it, there may be difficulties in obtaining user status data. If obtaining user login status data cannot be achieved, then all I can think of is to create a job

delete from dbo.UserPackages where ExpiryDate <getdate()  

so that the expired data will be deleted immediately after expiration,of course, it will be deleted after the job is executed.

In addition, when you ask questions, it is best to publish all the tables and data involved, which will help the experts to do some tests and quickly solve your problems.
Also, after you post the question, if you want to update the question, it is best to update in the comment, so as not to disrupt the discussion thread, which is also not conducive to the solution of the problem.

Best Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.