freezegravity
asked on
What would be the best way to update afield monthly in SQL Server 2005?
Hello:
I have two related questions:
1. I have a datetime field in my already existing table called "next_billing_date" and I want this date to be updated monthly to the next month. So, for example, if it has entered 10/19/2007 ... on 10/19/2007, I want the field to be updated to 11/19/2007 (or whenever the next month date might be). How can I schedule this?
2. There are some fields that are really old in that table. So, I want the field to come to the latest "month." I mean, if the field has 05/01/2006, I want the field to be updated to 11/something/2007 because that would be the next month closest month starting from 05/01/2006 (as in, the date couldn't have passed already).
Hopefully, you all understand my problem and can suggest a relatively painless solution.
THANKS!
I have two related questions:
1. I have a datetime field in my already existing table called "next_billing_date" and I want this date to be updated monthly to the next month. So, for example, if it has entered 10/19/2007 ... on 10/19/2007, I want the field to be updated to 11/19/2007 (or whenever the next month date might be). How can I schedule this?
2. There are some fields that are really old in that table. So, I want the field to come to the latest "month." I mean, if the field has 05/01/2006, I want the field to be updated to 11/something/2007 because that would be the next month closest month starting from 05/01/2006 (as in, the date couldn't have passed already).
Hopefully, you all understand my problem and can suggest a relatively painless solution.
THANKS!
SQL_SERVER_DBA
select datepart(MM, getdate())+1
ASKER
Ok ... but now ...
1. How do I schedule it?
2. How can I bring the rest of the fields up-to-date?
Thanks!
1. How do I schedule it?
2. How can I bring the rest of the fields up-to-date?
Thanks!
ASKER
SQL_SERVER_DBA:
I tested your query, and it just gives the next month. Not the month date. As in, it tells me the next month is 11, I want something like 11/08/2007
Thanks!
I tested your query, and it just gives the next month. Not the month date. As in, it tells me the next month is 11, I want something like 11/08/2007
Thanks!
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
seems like a really good idea. However, the client just decided to forego this requirement and just went with a brand new database and design. But, it is a very plausible solution!