Hello everyone,
I am getting date in yyyy-mm-dd format,before loading to target I need to add 1 month to existing date.
For ex : 2011-05-31 need to load as 2011-06-30
2010-12-21 need to load as 2011-01-21.
Could any one suggest me the approach please........
Thanks in advance
Need to add 1 month to date
Moderators: chulett, rschirm, roy
Re: Need to add 1 month to date
Add this function in date column
ADD_MONTHS(TO_DATE(SYSDATE),1)
ADD_MONTHS(TO_DATE(SYSDATE),1)
Thanks & Regards
Lohit Iti
Lohit Iti
-
- Participant
- Posts: 26
- Joined: Thu Aug 12, 2010 12:07 am
Re: Need to add 1 month to date
I think the following Stage variables will help you
svNextMonth -
If ( Compare(MonthFromDate(Input_Date),'12') = 0 ) Then '01'
Else DecimalToString( (StringToDecimal(MonthFromDate(Input_Date)) + 1) ,'suppress_zero')
svNextMonthDate -
If ( Compare(MonthFromDate(Input_Date),'12') = 0 )
Then
DecimalToString( (StringToDecimal(YearFromDate(Input_Date)) + 1) ,'suppress_zero') :'-': ( If Len(svNextMonth) = 2
Then svNextMonth Else '0' : svNextMonth ) :MonthDayFromDate(Input_Date)
Else
YearFromDate(Input_Date):'-': ( If Len(svNextMonth) = 2 Then svNextMonth Else '0' : svNextMonth ) :MonthDayFromDate(Input_Date)
svNextMonth -
If ( Compare(MonthFromDate(Input_Date),'12') = 0 ) Then '01'
Else DecimalToString( (StringToDecimal(MonthFromDate(Input_Date)) + 1) ,'suppress_zero')
svNextMonthDate -
If ( Compare(MonthFromDate(Input_Date),'12') = 0 )
Then
DecimalToString( (StringToDecimal(YearFromDate(Input_Date)) + 1) ,'suppress_zero') :'-': ( If Len(svNextMonth) = 2
Then svNextMonth Else '0' : svNextMonth ) :MonthDayFromDate(Input_Date)
Else
YearFromDate(Input_Date):'-': ( If Len(svNextMonth) = 2 Then svNextMonth Else '0' : svNextMonth ) :MonthDayFromDate(Input_Date)
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: