How to expire a particular row in SQL Server once a date is reached?
asp.net, sql-server, sql-server-2008-express
Solution
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.
Problem
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 .