How to increment the date column by a month

[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

[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!!

[deepa_shenoy]

Thanks Amey, will try this approach.