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

asp.net sql-server sql-server-2008-express
vivek picture vivek · Aug 23, 2012 · Viewed 10.6k times · Source

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

Curt picture Curt · Aug 23, 2012

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

Related questions

Call a stored procedure with parameter in c#

I can do a delete, insert and update in my program and I try to do an insert by call a created stored procedure from my database. This a button insert I make work well. private void btnAdd_Click(object …

Read More
How can I solve a connection pool problem between ASP.NET and SQL Server?

The last few days we see this error message in our website too much: "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and …

Read More
Cannot open database "test" requested by the login. The login failed. Login failed for user 'xyz\ASPNET'

I have created a web service which is saving some data into to db. But I am getting this error: Cannot open database "test" requested by the login. The login failed. Login failed for user 'xyz\ASPNET'. My connection string …

Read More