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 .

What do you mean by "expire"? Delete from the database? Mark a field as "expired"? — robert, Aug 23 '12 at 12:18
@Meryovi I guess may be he wants to delete after sometime automatically. — Waqar Janjua, Aug 23 '12 at 12:30
I just want to show the user that his purchased package is now expired, that can be done by updating the status as expired. but when should i fire this update query? that's the problem. — vivek, Aug 24 '12 at 06:29

score 13 · Answer 1 · answered Aug 23 '12 at 12:18

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()

score 1 · Accepted Answer · answered Aug 23 '12 at 12:20

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.

May be windows service can work in this case. I will try this option first. — vivek, Aug 24 '12 at 06:29

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

2 Answers2