hi
I am wondering if DataStage has a simple function to do the same thing as oracle function ADD_MONTHS()
Thanks
xli
Add month to a given date
Moderators: chulett, rschirm, roy
I don't believe so, but someone posted a function on this forum before which will do the trick as ADD_MONTHS, see below
FUNCTION AddMonthsToDate(BaseDate, Months)
Equate RoutineName To "AddMonthsToDate"
Ans = @NULL
Test = Iconv(BaseDate, "DYMD")
If Status() = 0 And BaseDate Matches "8N" Then
If Months Matches "1N0N" : @VM : "'+'1N0N"
Then
Year = BaseDate[1,4]
Month = BaseDate[5,2]
Day = BaseDate[7,2]
Month += 6
If Month > 12
Then
Month -= 12
Year += 1
End
Ans = Fmt(Year,"R%4") : Fmt(Month("R%2") : Day
End
Else
Call DSTransformError("Non-integer months in Arg2.", RoutineName)
End
End
Else
Call DSTransformError("Invalid date " : Quote(BaseDate), RoutineName)
End
RETURN(Ans)
James
FUNCTION AddMonthsToDate(BaseDate, Months)
Equate RoutineName To "AddMonthsToDate"
Ans = @NULL
Test = Iconv(BaseDate, "DYMD")
If Status() = 0 And BaseDate Matches "8N" Then
If Months Matches "1N0N" : @VM : "'+'1N0N"
Then
Year = BaseDate[1,4]
Month = BaseDate[5,2]
Day = BaseDate[7,2]
Month += 6
If Month > 12
Then
Month -= 12
Year += 1
End
Ans = Fmt(Year,"R%4") : Fmt(Month("R%2") : Day
End
Else
Call DSTransformError("Non-integer months in Arg2.", RoutineName)
End
End
Else
Call DSTransformError("Invalid date " : Quote(BaseDate), RoutineName)
End
RETURN(Ans)
James
denzilsyb, what I mean is add/minus n months to a given date, it's exactly what the oracle add_months() function does.
jclin777, thanks for your post, I know I can created a routine to handle it. I was just wondering if there is a simple built-in funciton I haven't discover to do it. However, it seems the answer is nagative.
cheers, xli
jclin777, thanks for your post, I know I can created a routine to handle it. I was just wondering if there is a simple built-in funciton I haven't discover to do it. However, it seems the answer is nagative.
cheers, xli
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
There is definitly no intrinsic function for any form of interval handling.
Of course, once you've built the Routine, it's built-in for ever more! (Looks kinda like one of mine.)
Of course, once you've built the Routine, it's built-in for ever more! (Looks kinda like one of mine.)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.