SQLTeam.com | Weblogs | Forums

Automatic expire if expired date is today


#1

Hello everyone can you please help me with my code problem. Im using sqlserver vb.net. I want to automatically update in database all the expired membership today. please help me guys! Thanks a lot!

for example the end date of the membership is 2016/12/08. I want that if the date is 2016/12/09 all the expired membership or active membership from the previous day will automatically change the Status from Active to Expired. Thanks Guys :slight_smile:


#2

can you run this query and confirm if the output is correct(you need to modify the query as per the columns in your table),if it is correct you can use this to update only the effected records.There are many other ways to do it.

select memebershipid,
case
when convert(varchar(10),yourdatecolumngoeshere,101)=convert(varchar(10),getdate(),101)
then 'expired'
else 'not expired'
end as col
from your table


#3

Two thoughts:

  1. You could have a scheduled task that runs at, say, midnight that changes the status on all expired membership records

  2. You could have a VIEW (or a Computed Column, but I tend not to like them very much) which has a Pseudo Column for the Membership Status which is True/False depending on whether the Expiry date is past-due. When you query the VIEW you can use that "IsExpired" status pseudo-column in your query like any other column. This is never wrong (not even at 1 millisecond after midnight :slight_smile: )


#4

for example the end date of the membership is 2016/12/08. I want that if the date is 2016/12/09 all the expired membership or active membership from the previous day will automatically change the Status from Active to Expired. Thanks Guys :slight_smile: