How to update a row values at a specific time in SQL Server?

Question

I want to change a row's values when a specific time happens in SQL Server

customerId  isBlock       time
-------------------------------  
1           false         12:00

When the time 12:00 happens, dynamically will change isBlock value to true

Is `time` the current time (i.e. at 12:00 PM every day, change the value of the column), or a column within the table (when the value is 12:00 PM, `isBlock` must be set to true)? — yaakov, Feb 20 '16 at 10:59
You probably want some sort of a scheduled job then - see [this question](http://stackoverflow.com/questions/5471080/how-to-schedule-a-job-for-sql-query-to-run-daily) for how to do it as a SQL Job. — yaakov, Feb 20 '16 at 11:06
There are two way to do this: One is creating a service continuously running in background that awakes roughly at the 12 o'clock and works on your db. The other one is throuth the [Sql Server Agent service](https://technet.microsoft.com/en-us/library/ms181153(v=sql.105).aspx) creating the appropriate job and execute a stored procedure — Steve, Feb 20 '16 at 11:06

score 2 · Accepted Answer · answered Feb 20 '16 at 13:07

Probably the simplest way to do this is to use a computed column (or view). So:

alter table t
    add column isBlock (case when getdate() > time then 'true' else 'false' end);

Notes:

The original column is not needed for this logic.
I am assuming that "time" is really some sort of date/time value, so the comparison to getdate() is appropriate.

1 Answers1