Hi All,
I would like to know how to increment the date column by a month using the Transformer Stage.
Thanks in advance.
Deepa
How to increment the date column by a month
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 95
- Joined: Thu Sep 24, 2009 12:15 am
- Location: India
-
- Participant
- Posts: 334
- Joined: Fri Dec 01, 2006 5:17 am
- Location: Texas
Hi,
This might help you![Wink ;)](./images/smilies/icon_wink.gif)
You have to use following stage variables:
The ouptut is :
Thanks!!
This might help you
![Wink ;)](./images/smilies/icon_wink.gif)
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")
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
http://findingjobsindatastage.blogspot.com/
Theory is when you know all and nothing works. Practice is when all works and nobody knows why. In this case we have put together theory and practice: nothing works. and nobody knows why! (Albert Einstein)
Theory is when you know all and nothing works. Practice is when all works and nobody knows why. In this case we have put together theory and practice: nothing works. and nobody knows why! (Albert Einstein)
-
- Participant
- Posts: 95
- Joined: Thu Sep 24, 2009 12:15 am
- Location: India