3

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
JanLeeYu
  • 981
  • 2
  • 9
  • 24

1 Answers1

2

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
 );
Mithrandir
  • 24,869
  • 6
  • 50
  • 66
  • 1
    The are many good tutorials on creating a sql server agent job. I think this is well beyond the scope of a stack overflow answer. – Mithrandir Mar 30 '16 at 09:15
  • Here are the Microsoft [docs on creating a job](https://msdn.microsoft.com/en-gb/library/ms190268.aspx). There are also numerous blog posts that include examples. A quick [google](https://www.google.co.uk/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=how%20to%20create%20a%20sql%20server%20agent%20job) should return a few. – David Rushton Mar 30 '16 at 09:47