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

Posted on 2007-10-08
Last Modified: 2012-05-05

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.

Question by:freezegravity
    LVL 16

    Expert Comment

    select datepart(MM, getdate())+1


    Author Comment

    Ok ... but now ...

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


    Author Comment


    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


    LVL 35

    Accepted Solution


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

    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 )


    Author Closing Comment

    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!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now