Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to dynamically increment by one month each tim
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Aged Yak Warrior

567 Posts

Posted - 11/25/2012 :  05:16:41  Show Profile  Reply with Quote
I am trying to create a sql procedure, this procedure will be used by ETL tool.

procedure will extract data for one month, each time, if i ran the procedure for oct 2012.
I Want to use between created_date >='10/01/2012' and created_date <='10/31/2012'

How does the procedure increment to one month based on values
month: oct
yr= 2012

Is it possible based on those two values can it increment to Nov
Month= nov

These two values i want to store in table, which ever it is curently running.

On the next call it has to increment extacly by one month.

Is it possible or may be any diff logic is better, please kindly provide any ideas to handle this date range increment by a month.

Thank you very much for the helpful information.

Elizabeth B. Darcy
Starting Member

United Kingdom
39 Posts

Posted - 11/25/2012 :  08:40:01  Show Profile  Reply with Quote
A few thoughts that I have are as follows:

1. Don't store the month and year values as you indicated (Oct, 2012). Store them as real dates. For example, store them in a table with a column that is of date type and store the first day of the month of interest. (20121001 for Oct 2012).

2. Prefer using something like create_date >= '10/1/2012' and create_date < '11/1/2012' over what you proposed (created_date >='10/01/2012' and created_date <='10/31/2012').

3. If you store the dates like I said in my bullet point 1 above, then computing the beginning and end dates is simple:
create date >= datefromTable and create_date < dateadd(mm,1,datefromTable)

4. What you said about "On the next call it has to increment extacly by one month" - it would be better to advance that date from the ETL tool rather than having SQL remember what the last call was and then increment it. Error recovery, avoiding incorrect data in a multi-user environment etc. being my reasons for suggesting so. That is assuming that you are making a call for each month from the ETL tool. If you are simply getting the data for all the months in one call from the ETL tool, then of course, it is better to do the date progression in SQL

-- Yes, I am indeed a fictional character.
Go to Top of Page

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 11/25/2012 :  09:50:59  Show Profile  Reply with Quote

for incrementing the date eachtime during ETL run create a control table to log date used during each ETL run. Each run will have a step which retrieves date used by last run and will add 1 month to it to advance to next month

SQL Server MVP

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000