CODEWITHSUNDEEP
sql-serverauto-update
JanLeeYu
JanLeeYu

Reputation: 1001

Automatic update of values in SQL Server

For example I have a table tb with columns :

order_id | date_ordered | due_date | status

Are there any out of the box solution where I can automatically update status column when the current time (from server) reaches the value of the due_date column? How do I do it?

Thanks in advance.

UPDATE :

Something like this :

test1 | 2016-03-30 09:19:06.610 | 2016-03-30 11:19:06.610 | NEW
test2 | 2016-03-30 09:22:43.513 | 2016-03-30 11:22:43.513 | NEW
test3 | 2016-03-30 09:06:03.627 | 2016-03-30 11:06:03.627 | NEW

When the server time reaches 2016-03-30 11:19:06.610, test1's status will change value say, overdue

Upvotes: 3

Views: 1285

Answers (1)

Mithrandir
Mithrandir

Reputation: 25337

It depends on what you mean by "out of the box solution". You could create a sql server agent job, which checks every minute if the value due_date is less or equal to the current date and time and change the state column.

A computed column might be another, much simpler solution.

A table like this might suffice:

CREATE TABLE tb_test (
    order_id INT PRIMARY KEY,
    date_ordered  DATETIME,
    due_date DATETIME,
    [status] as 
    CASE WHEN due_date <= GETDATE() THEN 'overdue'
    ELSE 'new' END
 );

Upvotes: 2

Related Questions