SubtractMonth Routine

[jeredleo]

All,
Does anyone have a Month subtraction routine that might be willing to share? I really wish DataStage would include some of these very useful and common routines. If not thanks anyway.

Thanks,
JB

[msigal]

What exactly do you want to subtract, Date - x months = new date? There are plenty of date manipulation options, Iconv Oconv functions and ConvertMonth function to name a couple. The only thing I think you would need to watch for is the last few days of the month. What to do if March 30 - 1 month? If you simply take the date, 2002/03/30 and manipulate the month and push it though Iconv and Oconv you can check status codes for the things like 2002/02/30. And you can put some logic in to check if the resulting month value is negative. Here's a start...


InDate = 2002/03/30
InSubtractMo = 4

Yr = Field(InDate,"/",1)
Mo = Field(InDate,"/",2)
Dy = Field(InDate,"/",3)

* Check for Prior Year and subtract months
If Mo - InSubtractMo < 1 THEN
Mo = Mo + 12 - InSubtractMo
Yr = Yr - 1 - (Int(InSubtractMo/12))
End Else
Mo = Mo - InSubtractMo
End

*Set new date
NewDate = Yr:"/":Mo:"/":Dy

*Now mess around with Date Validation
Loop
InternalDate = Iconv(NewDate,"D4/YMD")
IStatus = Status()
If Istatus <> 3 Then
Exit
End Else
*roll back date one day
NewDate = Yr:"/":Mo:"/":(Dy-1)
End
Repeat



Myles Sigal
Technical Analyst
The Medstat Group
Ann Arbor, MI 48108
734-913-3466

[JeanPierreHaddad]

I have been using this one for a while. Hope it will help you.

COMMON /MP/Month(12)

* Takes a date in timestamp format and adds a number of days, months * * or years according to the 3rd argument:
* "D" = days
* "M" = months
* "Y" = years

Month(1)=31
Month(2)=28
Month(3)=31
Month(4)=30
Month(5)=31
Month(6)=30
Month(7)=31
Month(8)=31
Month(9)=30
Month(10)=31
Month(11)=30
Month(12)=31
CurrentYear=Arg1[1,4]
IF Mod(CurrentYear,4) = 0 THEN Month(2)=29

If Arg1 <> "" then
InYear = Arg1[1,4]
InMonth = Arg1[6,2]
InDay = Arg1[9,2]

FromDate = iconv(Arg1, "D YMD")

Begin Case

Case Arg3 = "D"
ToDate = FromDate + Arg2
Ans = oconv(ToDate, "D-YMD[,2,2]"):" 00:00:00.000"

Case Arg3 = "M"
Months = InMonth + Arg2
If Months > 12 then
InYear = InYear + Div(Months, 12)
InMonth = Mod(Months ,12)
If InMonth = 0 Then
InMonth = 12
InYear = InYear - 1
End
End Else if Months =< 0 then
Months = -Months
InYear = InYear - Div(Months, 12) - 1
InMonth = 12 - Mod(Months ,12)
If InMonth = 0 Then
InMonth = 12
InYear = InYear - 1
End
End Else
InMonth = Months
End
If InDay=31 then InDay=Month(InMonth)
T = IConv(InYear:" ":InMonth:" ":InDay,"D YMD")
Ans = Oconv(T,"D":Arg1[5,1]:"YMD[,2,2]"):" 00:00:00.000"

Case Arg3 = "Y"
InYear = InYear + Arg2
Ans = InYear:Arg1[5,1]:InMonth:Arg1[8,1]:InDay:" 00:00:00.000"

End Case

End Else
Ans = "ERROR - NO DATE"
End

[JeanPierreHaddad]

I have been using this one for a while. Hope it will help you.

COMMON /MP/Month(12)

* Takes a date in timestamp format and adds a number of days, months * * or years according to the 3rd argument:
* "D" = days
* "M" = months
* "Y" = years

Month(1)=31
Month(2)=28
Month(3)=31
Month(4)=30
Month(5)=31
Month(6)=30
Month(7)=31
Month(8)=31
Month(9)=30
Month(10)=31
Month(11)=30
Month(12)=31
CurrentYear=Arg1[1,4]
IF Mod(CurrentYear,4) = 0 THEN Month(2)=29

If Arg1 <> "" then
InYear = Arg1[1,4]
InMonth = Arg1[6,2]
InDay = Arg1[9,2]

FromDate = iconv(Arg1, "D YMD")

Begin Case

Case Arg3 = "D"
ToDate = FromDate + Arg2
Ans = oconv(ToDate, "D-YMD[,2,2]"):" 00:00:00.000"

Case Arg3 = "M"
Months = InMonth + Arg2
If Months > 12 then
InYear = InYear + Div(Months, 12)
InMonth = Mod(Months ,12)
If InMonth = 0 Then
InMonth = 12
InYear = InYear - 1
End
End Else if Months =< 0 then
Months = -Months
InYear = InYear - Div(Months, 12) - 1
InMonth = 12 - Mod(Months ,12)
If InMonth = 0 Then
InMonth = 12
InYear = InYear - 1
End
End Else
InMonth = Months
End
If InDay=31 then InDay=Month(InMonth)
T = IConv(InYear:" ":InMonth:" ":InDay,"D YMD")
Ans = Oconv(T,"D":Arg1[5,1]:"YMD[,2,2]"):" 00:00:00.000"

Case Arg3 = "Y"
InYear = InYear + Arg2
Ans = InYear:Arg1[5,1]:InMonth:Arg1[8,1]:InDay:" 00:00:00.000"

End Case

End Else
Ans = "ERROR - NO DATE"
End