Geek Answers Handbook

How to expire a specific row in SQL Server after a date is reached?

This may be a very simple scenario for experienced developers, but as a newbie, I have been hanging for a long time. I create a site in asp.net where the user can purchase packages. These packages have a certain expiration date (for example, 2 months after purchase, 6 months, etc.).

The problem is how each package expired exactly on the same day and time that I calculated based on the purchase date. How to do it?

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

+4
source share
2 answers

Depends on what you mean by expiration - whether you want to delete the data, mark the record or just hide the data from the user's screen.

In any case, you need to add the expiration date as a column to the table (? Packages) and save the estimated expiration date in this column.

You have options such as:

  • Starting a task to delete / expire a record after its expiration, i.e. CURRENT_TIMESTAMP is > ExpiryDate (SQL Express does not have an SQL agent, so you will need to, for example, write a Windows service or connect to the Windows task scheduler to do this).
  • Or change your application to “check” the value of ExpiryDate (for example, DateTime.Now >= ExpiryDate ), and then block the user from / outside the package from the user interface.
0
source

Instead of deleting records, you can put a WHERE in a place that excludes records depending on their expiration date:

 SELECT PackageName FROM Package WHERE ExpiryDate>GETDATE()

However, if you still want to remove them from the database, you can place an SQL Job that runs every day / hour, etc. which will delete the expired entries:

 DELETE FROM Package WHERE ExpiryDate<GETDATE()
+6
source

More articles:


All Articles