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

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.