How to expire a particular row in SQL Server once a date is reached?

Question

It may be very simple scenario for experienced developers, but as a beginner I have been hanging around this for a long time. I am creating a website in asp.net where a user can purchase a packages. These packages have some expiry date associated with them (e.g. 2 months after purchase, 6 months, etc.).

The problem is how to expire each package exactly at the same date and time that i have calculated on the basis of purchase date. How to do this thing?

I am using SQL Server express edition 2008 and asp.net 4.0 .

Answer 1

Rather than deleting the records, you could put a WHERE clause in place which would exclude the records based on their expiry date:

SELECT PackageName
FROM Package
WHERE ExpiryDate>GETDATE()

However if you still wish to remove them from the database, you could put a SQL Job in place which is ran every day/hour etc which would delete records which have expired:

DELETE FROM Package
WHERE ExpiryDate<GETDATE()

Answer 2

Depends a bit on what you mean by expire - do you want to delete the data, flag the record, or just hide the data from the user's screen.

Either way, you need to add the expiry date as a column to your table (?packages), and save the calculated expiry date into this column.

You then have options such as :

• Run a job to delete / expire the record once it has expired, i.e. CURRENT_TIMESTAMP is > ExpiryDate (SQL Express doesn't have SQL Agent, so you would need to e.g. write a windows service, or hook into Windows Task Scheduler to do this).
• Or, change your application to 'check' the value of the ExpiryDate (e.g. DateTime.Now >= ExpiryDate), and then lock the user out / hide the package from the UI.