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

Expand|Select|Wrap|Line Numbers

 UPDATE tablename SET status = 'deleted' 

WHERE datecreated < GETDATE() - 365

or this

Expand|Select|Wrap|Line Numbers

 UPDATE tablename SET status = 'deleted' 

WHERE datecreated < GETDATE() - 12 MONTH

But double check this

ck9663

also try:

Expand|Select|Wrap|Line Numbers

       UPDATE tablename SET status = 'deleted'

      WHERE datediff(yy, datecreated,GETDATE()) = 1

Change the condition as necessary.

-- CK

vikasr2002

Quote:

Originally Posted by code green

I think this is OK

Expand|Select|Wrap|Line Numbers

 UPDATE tablename SET status = 'deleted' 

WHERE datecreated < GETDATE() - 365

or this

Expand|Select|Wrap|Line Numbers

 UPDATE tablename SET status = 'deleted' 

WHERE datecreated < GETDATE() - 12 MONTH

But double check this

This query is fine. I want it scheduled(automated) . 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

Quote:

Originally Posted by ck9663

also try:

Expand|Select|Wrap|Line Numbers

       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(automated) . 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

Update Status column after specific date.

re: Update Status column after specific date.

re: Update Status column after specific date.

re: Update Status column after specific date.

re: Update Status column after specific date.

re: Update Status column after specific date.

re: Update Status column after specific date.

Similar Microsoft SQL Server bytes

/bytes/development

/bytes/it

/bytes/about