?
Solved

What would be the best way to update afield monthly in SQL Server 2005?

Posted on 2007-10-08
5
Medium Priority
?
141 Views
Last Modified: 2012-05-05
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!
0
Comment
Question by:freezegravity
  • 3
5 Comments
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20037028
select datepart(MM, getdate())+1


0
 

Author Comment

by:freezegravity
ID: 20037043
Ok ... but now ...

1. How do I schedule it?
2. How can I bring the rest of the fields up-to-date?

Thanks!
0
 

Author Comment

by:freezegravity
ID: 20037105
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!

0
 
LVL 35

Accepted Solution

by:
David Todd earned 2000 total points
ID: 20038563
Hi,

instead of
select datepart(MM, getdate())+1

try
select dateadd( month, 1, getdate())

Also the datepart solution breaks down around the beginning and end of the year.

To schedule this, you could use a SQL Agent job. Have you used SQL Jobs before?

In fact, if the dates are as far out as you suggest, I'd schedule it to run each night - (the below assumes you are running before mid-night)

update sometable
set somedatefield = dateadd( month, 1, somedatefield )
where somedatefield < dateadd( day, datediff( day, 0, getdate()), 0 )

HTH
  David
0
 

Author Closing Comment

by:freezegravity
ID: 31408083
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!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Loops Section Overview

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question