How to update column value when system datetime reach datetime saved in other column? in sqlserver

Question

I have one table with 2 columns. One is called "houranddate" and is datetime type and the other is called "status" and is int type. I want the value in "status" column to be updated to "1" when the the server datetime reaches the same datetime value saved int the column "houranddate".

Is there any chance that I set this in sql server MS or the only way is to use some web service?

Answer 1

In SQL Server, you can use a computed column:

alter table t
    add new_status as (case when datetimecol >= getdate() then 1 else status end);

(Or, what I would do in this case is name the existing column something like _status and the new one status.)

If status is 0 before that time, then drop the existing status column and instead:

alter table t
    add status as (case when datetimecol >= getdate() then 1 else 0 end);

The computed column is calculated when it is used. So, it is always up-to-date, without the use of jobs or update.