Page 1 of 1
How to increment the date column by a month
Posted: Sun Jan 03, 2010 10:35 pm
by deepa_shenoy
Hi All,
I would like to know how to increment the date column by a month using the Transformer Stage.
Thanks in advance.
Deepa
Posted: Mon Jan 04, 2010 4:41 am
by AmeyJoshi14
Hi,
This might help you
You have to use following stage variables:
Code: Select all
svrYear(Int)=If MonthFromDate(RUN_DT) <12 then YearFromDate(RUN_DT) else YearFromDate(RUN_DT) + 1
svrnextMonth(Int)=If MonthFromDate(DSLink2.RUN_DT) <12 then MonthFromDate(DSLink2.RUN_DT)+1 else 1
svrnexttonextMonth(Int)=If svrnextMonth<12 then svrnextMonth+1 else "01"
svrDay(Int)=MonthDayFromDate(DSLink2.RUN_DT)
svrDateCombination(varchar)=if len(svrnexttonextMonth)=2 then svrYear:"-":svrnexttonextMonth:"-01" else svrYear:"-0":svrnexttonextMonth:"-01"
svrCheckDay(Int)=MonthDayFromDate(DateFromDaysSince(-1,StringToDate(svrDateCombination,"%yyyy-%mm-%dd")))
svrfinalDay(varchar)=IF (svrnextMonth <> 2 and svrDay>svrCheckDay) then svrCheckDay else IF (svrnextMonth = 2 and svrDay>svrCheckDay) then svrCheckDay else svrDay
--------------
RUN_DT_ADDED_MONTH= IF (len(svrnextMonth) =2 and len(svrfinalDay)=2)
then
StringToDate(svrYear:"-":svrnextMonth:"-":svrfinalDay,"%yyyy-%mm-%dd")
else
if len(svrfinalDay)<2 then
StringToDate(svrYear:"-0":svrnextMonth:"-0":svrfinalDay,"%yyyy-%mm-%dd")
else
StringToDate(svrYear:"-0":svrnextMonth:"-":svrfinalDay,"%yyyy-%mm-%dd")
The ouptut is :
Code: Select all
Key_column,RUN_DT,RUN_DT_ADDED_MONTH
1,2009-10-11,2009-11-11
2,2009-11-22,2009-12-22
3,2009-12-30,2010-01-30
4,2010-01-31,2010-02-28
5,2009-07-07,2009-08-07
Thanks!!
Posted: Mon Jan 04, 2010 5:12 am
by deepa_shenoy
Thanks Amey, will try this approach.