question

rajnish avatar image
rajnish 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?
sql-server-2008querytsql
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

yes, Magnus Ahlkvist i want the Status to automatically changed to **"0"** from **"1"** when ExpiryDate passed. Tnaks for your suggestion **Anuj Narikkundil Balakrishnan** and **Magnus Ahlkvist**.
0 Likes 0 ·
@rajnish - I made your answer a part of your original question instead.
0 Likes 0 ·
@rajnish - if you do what Anuj suggests in his answer, your Status column will automagically change when Expire passes.
0 Likes 0 ·
Cyborg avatar image
Cyborg answered
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

5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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'
2 Likes 2 ·
CURRENT_TIMESTAMP is ANSI SQL standard - Joe Celko would love it :)
1 Like 1 ·
You beat me by a minute or so :)
0 Likes 0 ·
Why CURRENT_TIMESTAMP?
0 Likes 0 ·
Thanks for the clear explanation!! :)
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
I'm not sure I understand why you'd want Sql Agent job or a cursor. UPDATE [user] SET Status=0 WHERE ExpiryDate]]
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.