How to run queries based on a date column?

Question

I have the following table (example):

RESEARCH  | START_DATE          | END_DATE            | STATUS         
a         | 2013-11-10 19:00:00 | 2013-11-11 12:00:00 | Ready
b         | 2013-11-12 02:30:00 | 2013-12-01 13:30:00 | Ready
c         | 2013-11-05 07:00:00 | 2013-12-10 15:50:00 | Running

I need to change the status of researches from Ready to Running when START_DATE is reached, and from Running to Finished when END_DATE is reached.

Is there a way of doing this using only SQL Server 2008 R2 Express?

Linger · Answer 1 · 2013-11-06T18:25:56.207

0

UPDATE MyTable 
SET STATUS = 'Running' 
WHERE START_DATE >= CURRENT_TIMESTAMP

UPDATE MyTable 
SET STATUS = 'Finished' 
WHERE END_DATE >= CURRENT_TIMESTAMP

If you run the two queries above in that order then you will achieve what you are looking to do.

If you are going to run them out of order then the first UPDATE would have to be

UPDATE MyTable 
SET STATUS = 'Running' 
WHERE START_DATE >= CURRENT_TIMESTAMP 
AND END_DATE < CURRENT_TIMESTAMP

Or you could do something like the following to combine the two queries:

UPDATE MyTable 
SET STATUS  =  
CASE  
  WHEN START_DATE >= CURRENT_TIMESTAMP 
     AND END_DATE < CURRENT_TIMESTAMP  THEN 'Running' 
  WHEN END_DATE >= CURRENT_TIMESTAMP THEN 'Finished'
  ELSE 'Ready'
  END

edited Nov 06 '13 at 18:25

answered Nov 06 '13 at 18:15

Linger

14,942
23
52
79

I will need to run this query every minute. How to do that without using a job or task? – Francisco Reis Nov 06 '13 at 18:31
[Francisco Reis](http://stackoverflow.com/users/2765188/francisco-reis), you can not run query every minute without task. You have to create SQL Server Job and schedule it to run every minute. – Bogdan Bogdanov Nov 06 '13 at 18:34
**@Francisco Reis**, I just noticed that you are running the ***Express*** version of SQL Server. So, you can't use SQL Server Agent. This [***Answer***](http://stackoverflow.com/a/15371843/1253219) does give you some alternate options though. – Linger Nov 06 '13 at 18:48

Bogdan Bogdanov · Answer 2 · 2013-11-06T18:29:01.507

I think something like this will work for you:

update Table 
set 
    Status = case 
        when 
            CURRENT_TIMESTAMP  between START_DATE and END_DATE 
            and STATUS = 'Ready' then 'Running'
        when 
            CURRENT_TIMESTAMP  > END_DATE 
            and STATUS = 'Running' then 'Finished'
        else
            Status
        end;

Of course you have to be sure that statuses are ok, before starting to update.

How to run queries based on a date column?

2 Answers2