Update Status column after specific date.

[vikasr2002]

I have two columns in one table in database.
1. Status
2. DateCreated

Now if Todays date is one year after datecreated then change the Status to deleted.

How is it possible in SQL Server 2005. Is there any build in functionality for the same or we have to write external code ( C# ) to achive this.

Thanks

Vikas

[code green]

I think this is OK

UPDATE tablename SET status = 'deleted' 
WHERE datecreated < GETDATE() - 365

or this

UPDATE tablename SET status = 'deleted' 
WHERE datecreated < GETDATE() - 12 MONTH

But double check this

[ck9663]

also try:

      UPDATE tablename SET status = 'deleted'
      WHERE datediff(yy, datecreated,GETDATE()) = 1

Change the condition as necessary.

-- CK

[vikasr2002]

I think this is OK

UPDATE tablename SET status = 'deleted' 
WHERE datecreated < GETDATE() - 365

or this

UPDATE tablename SET status = 'deleted' 
WHERE datecreated < GETDATE() - 12 MONTH

But double check this

This query is fine. I want it scheduled(autom ated) . There are thousand of records and daily there will be records which will qualify for this condition.
How to schedule it in SQL Server 2005 that daily Status is changed of records who satisfy the condition.

Thanks
Vikas Rijhwani

[vikasr2002]

also try:

      UPDATE tablename SET status = 'deleted'
      WHERE datediff(yy, datecreated,GETDATE()) = 1

Change the condition as necessary.

-- CK

This query is fine. I want it scheduled(autom ated) . There are thousand of records and daily there will be records which will qualify for this condition.
How to schedule it in SQL Server 2005 that daily Status is changed of records who satisfy the condition.

Thanks
Vikas Rijhwani

[code green]

Your last question would be better re-posted.
Show your query and ask how to schedule it the way you want.

[ck9663]

1. Create an SP.

2. Create a job and schedule it to run once a day.

3. Monitor the results.

-- CK