This post has been wikified, any user with enough reputation can edit it.
asked
Update Status Field After Expiry Date
Consider the following table user Id char(10) ExpiryDate datetime Status bit Now, I want to update the Status to False as soon as the current date becomes ExpiryDate. I have searched it think it can be done using "SQL Agent" but my web host doesn't provide me access to that and i don't want to use cursors. If anyone have idea about it please share, i will be thankful. actually i have records like this Id Name Created Expire Status 1 abc 2012-07-24 2013-01-24 1 2 xyz 2012-04-23 2012-10-23 1 Now i want that on 2012-10-23 for Id 2 Status Automatically Change to 0 can it be possible without agent or cursor?
@rajnish - if you do what Anuj suggests in his answer, your Status column will automagically change when Expire passes.
if the data is huge and non-deterministic, I Prefer SQL Job that runs daily to update the "Status" column. In your case one option is to to make the "Status" column "Computed", But this may require a lot of code change in you INSERT or UPDATE procedures or queries.
ALTER TABLE MyTable
DROP COLUMN [Status]
ALTER TABLE MyTable
ADD [Status] AS CASE WHEN ExpiryDate < GETDATE() THEN 1 ELSE 0 END
Because CURRENT_TIMESTAMP is defined in ANSI SQL standard. However, with the new datatypes in SQL Server 2008, one should really get into the habit of using sysdatetime() instead, and store in datetime2, to get the possibility to store exact milliseconds (as opposed to datetime which can't store eg '2012-01-01 12:00:00.998' - it would round that to '2012-01-01 12:00:00.997'
I'm not sure I understand why you'd want Sql Agent job or a cursor. UPDATE [user] SET Status=0 WHERE ExpiryDate]]
11 People are following this question.
